Change PostgreSQL data directory on Windows
source: how to change PostgreSQL data directory?
- Stop Postgres (you can use the control panel to find the correct service name)
net stop <name_of_the_service>
- Make sure Postgres is not running (e.g. using ProcessMonitor)
- Remove the Windows service using
pg_ctl unregister -N <name_of_the_service>
- Make sure Postgres is not running
- Move the data directory to the new location (or maybe only copy it, so that you have a backup)
- Re-create the service using (this assigns
postgres
as the service name)pg_ctl register -N postgres -D c:\new\path\to\datadir
- Start the service
net start postgres
- run psql to verify that Postgres is up and running
psql -U postgres
- Verify the running server is using the new data directory
show data_directory;
Details on how to use pg_ctl
can be found in the manual here.
Trigger for updated_at
column
Function:
CREATE FUNCTION update_at_set_timestamp()
RETURNS TRIGGER AS $$
BEGIN
NEW.updated_at = now();
RETURN NEW;
END;
$$ language 'plpgsql';
Create trigger:
CREATE TRIGGER my_table_updated_at BEFORE UPDATE
ON my_table
FOR EACH ROW
EXECUTE FUNCTION update_at_set_timestamp();