Postgresql Notes

Configure connections Configure listen addresses in /etc/postgresql/17/main/postgresql.conf Configure what addresses can access which databases with which users in /etc/postgresql/17/main/pg_hba.conf Create a user and a database owned by them psql CREATE USER username WITH PASSWORD 'password'; CREATE DATABASE dbname OWNER owneruser; GRANT ALL PRIVILEGES ON DATABASE dbname TO username; \c dbname GRANT ALL ON SCHEMA public TO username; # for Discourse CREATE EXTENSION hstore; CREATE EXTENSION pg_trgm; CREATE EXTENSION unaccent; CREATE EXTENSION vector; # temporarily add superuser to restore user ALTER USER username WITH SUPERUSER; ALTER USER username WITH NOSUPERUSER; Connect via psql psql -h host -p port -U username -d dbname Migrate data directory Stop postgresql service cp -a source_data_directory destination_data_directory `chown -R postgres_user /destination_data_directory export PGDATA=destination_data_directory Changing data directory to destination_data_directory within…