# 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 ```console sudo /usr/pgadmin4/venv/bin/python3 /usr/pgadmin4/web/setup.py get-users ``` ### Set Password ```console 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). ```bash sudo -u postgres psql ``` ```sql -- 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. ```sql CREATE ROLE app_user WITH LOGIN PASSWORD 'strong_password_here' CREATEDB; ``` Then the app itself (or its migration tool) can run: ```sql 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: ```bash 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): ```bash 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:** ```bash 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`: ```bash psql -h localhost -p 5433 -U app_user -d app_db ``` - Manage individual clusters: ```bash 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.