Change PostgreSQL data directory on Windows

source: how to change PostgreSQL data directory?

  1. Stop Postgres (you can use the control panel to find the correct service name) net stop <name_of_the_service>
  2. Make sure Postgres is not running (e.g. using ProcessMonitor)
  3. Remove the Windows service using pg_ctl unregister -N <name_of_the_service>
  4. Make sure Postgres is not running
  5. Move the data directory to the new location (or maybe only copy it, so that you have a backup)
  6. Re-create the service using (this assigns postgres as the service name) pg_ctl register -N postgres -D c:\new\path\to\datadir
  7. Start the service net start postgres
  8. run psql to verify that Postgres is up and running psql -U postgres
  9. 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();