PostgreSQL Migrate Database Find Settings of existing Database sudo -u postgres psql --cluster /main -- Database-level info \l+ your_database -- Server-wide settings SHOW SERVER_ENCODING; SHOW LC_COLLATE; SHOW LC_CTYPE; -- Installed extensions \dx -- Example Output Name | Owner | Encoding | Collate | Ctype | Access privileges ----------+----------+----------+------------+------------+-------------------- your_database | appuser | UTF8 | en_US.utf8 | en_US.utf8 | Export existing Database sudo -u postgres pg_dump -h -p -U -W -Fc -f -h = Hostname -p = Port -U = Username -W = Prompt for Password -Fc = pg_dump non-Text-Format (required for pg_restore) -f = Output File Command needs to be run with sudo -u postgres as the postgres User has (as default) only local Access and only direct Login without any Password. (Optional) Copy to new Server scp @:/tmp/ Create User on new Database sudo -u postgres psql --cluster /main -c "CREATE USER WITH PASSWORD '';" Create (empty) Database Restore Database sudo -u postgres pg_restore -h -p -U -d -c -h = Hostname -p = Port -U = Username -W = Prompt for Password -d = (New) Database Name -c = Input File Backup Database Find Database to Backup 1. List all Postgres Instances: pg_lsclusters root@SRV-PSQL-PRD-01:~# pg_lsclusters perl: warning: Setting locale failed. perl: warning: Please check that your locale settings: LANGUAGE = (unset), LC_ALL = (unset), LANG = "de_DE.UTF-8" are supported and installed on your system. perl: warning: Falling back to the standard locale ("C"). Ver Cluster Port Status Owner Data directory Log file 13 main 5432 online postgres /var/lib/postgresql/13/main /var/log/postgresql/postgresql-13-main.log 15 main 5433 online postgres /var/lib/postgresql/15/main /var/log/postgresql/postgresql-15-main.log 2. Connect to Cluster sudo -u postgres psql --cluster / root@SRV-PSQL-PRD-01:~# sudo -u postgres psql --cluster 15/main perl: warning: Setting locale failed. perl: warning: Please check that your locale settings: LANGUAGE = (unset), LC_ALL = (unset), LANG = "de_DE.UTF-8" are supported and installed on your system. perl: warning: Falling back to the standard locale ("C"). could not change directory to "/root": Permission denied psql (15.14 (Debian 15.14-1.pgdg12+1)) Type "help" for help. 3. List Databases \du postgres=# \du List of roles Role name | Attributes | Member of -----------+------------------------------------------------------------+----------- forgejo | Create DB | {} postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {} zabbix | 4. Disconnect \q Create Backup of Database Access Instance by Port 1. Identify Instance Port pg_lsclusters root@db-psql-prd-01:~# pg_lsclusters Ver Cluster Port Status Owner Data directory Log file 13 main 5432 online postgres /var/lib/postgresql/13/main /var/log/postgresql/postgresql-13-main.log 15 main 5433 online postgres /var/lib/postgresql/15/main /var/log/postgresql/postgresql-15-main.log 18 main 5434 online postgres /var/lib/postgresql/18/main /var/log/postgresql/postgresql-18-main.log 2. Open Connection to specific Instance using Port Host-to-Host Connections psql -h -p -U Localhost Connections using local-only Authentication sudo -u postgres psql -P [...] pgadmin4 Reset Admin Password List Users sudo /usr/pgadmin4/venv/bin/python3 /usr/pgadmin4/web/setup.py get-users Set Password sudo /usr/pgadmin4/venv/bin/python3 /usr/pgadmin4/web/setup.py update-user --password --role Create Application Database Creating a Postgres Database for an Application (CLI) Option 1: DBA creates the database directly Use this when the app should not be able to create/drop databases itself (recommended for most production apps). sudo -u postgres psql -- Create a dedicated role for the app CREATE ROLE app_user WITH LOGIN PASSWORD 'strong_password_here'; -- Create the database, owned by that role CREATE DATABASE app_db OWNER app_user; -- Restrict connections to only this DB for that user REVOKE ALL ON DATABASE app_db FROM PUBLIC; GRANT CONNECT ON DATABASE app_db TO app_user; -- Optional: default schema privileges \c app_db GRANT ALL ON SCHEMA public TO app_user; Use case: app just needs a schema to read/write. Least privilege, no CREATEDB right. Option 2: Role with CREATEDB permission Use this when the app (or a deployment pipeline / ORM migration tool) needs to create its own database, e.g. CI/CD, multi-tenant provisioning. CREATE ROLE app_user WITH LOGIN PASSWORD 'strong_password_here' CREATEDB; Then the app itself (or its migration tool) can run: CREATE DATABASE app_db OWNER app_user; Trade-off: convenient for automation, but CREATEDB lets that role create any number of databases on the cluster — scope it to a dedicated role per app, never reuse across apps. Adjusting pg_hba.conf Location depends on install method: sudo -u postgres psql -c "SHOW hba_file;" Add a line above any broader/catch-all rule (order matters — first match wins): # TYPE DATABASE USER ADDRESS METHOD host app_db app_user 10.0.0.0/24 scram-sha-256 Use scram-sha-256 (not md5 ) unless you have a compatibility reason not to. Restrict ADDRESS to the actual app subnet/host — avoid 0.0.0.0/0 . For local Unix-socket app connections on the same host: local app_db app_user scram-sha-256 Reload (no restart needed): sudo -u postgres psql -c "SELECT pg_reload_conf();" # or sudo systemctl reload postgresql Managing Multiple Postgres Instances on One Host (e.g. v15 + v18) Debian/Ubuntu's postgresql-common framework handles this natively via clusters , each with its own port, data dir, config, and pg_hba.conf . List all clusters: pg_lsclusters Example output: Ver Cluster Port Status Owner Data directory Log file 15 main 5432 online postgres /var/lib/postgresql/15/main ... 18 main 5433 online postgres /var/lib/postgresql/18/main ... Key points: Each version gets its own port automatically (5432, 5433, ...) — no manual port juggling needed. Config files live under /etc/postgresql/// — pg_hba.conf and postgresql.conf are per-instance , edit the correct version's file. Connect to a specific instance with -p : psql -h localhost -p 5433 -U app_user -d app_db Manage individual clusters: sudo pg_ctlcluster 18 main reload sudo systemctl restart postgresql@18-main If not using Debian's cluster tooling (e.g. compiled from source or RPM-based), you must manually assign distinct port , data_directory , and unix_socket_directories per instance in each postgresql.conf , and run each as a separate systemd service/data dir. Recommendation: decide per-app which major version it targets, keep app roles/DBs isolated to one cluster, and never share a pg_hba.conf between versions — each instance's file only affects its own cluster.