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 postgresql.conf
• pg_ctl start