Often used psql shell commands
\? [commands] show help on backslash commands
\q quit psql
\c[onnect] {[DBNAME|- USER|- HOST|- PORT|-] | conninfo} connect to new database
\l[+] [PATTERN] list databases
\d[S+] list tables, views, and sequences
\du[S+] [PATTERN] list roles
\password [USERNAME] securely change the password for a user
Create a user
create user <username> with encrypted password '<password>';
Create a database with a specific owner
create database "<dbname>" with owner = <username>;
Dump database
pg_dumpall -U postgres > dump.sql
Dump database only schema (no data)
pg_dumpall -U postgres -s > dump.sql
Restore database
psql -U postgres < dump.sql
Update JSON field
UPDATE test SET data = jsonb_set(data, '{name}', '"my-other-name"');
Upgrade major version with docker containers
docker exec postgres pg_dumpall -U postgres > dump.sql
docker rm -vf postgres
cp -a /var/lib/docker/volumes/postgres_data/_data ~
rm -rf /var/lib/docker/volumes/postgres_data/_data/*
# create and run docker container with new version
cp ~/dump.sql /var/lib/docker/volumes/postgres_data/_data
docker exec -ti postgres /bin/bash
cd /var/lib/postgresql/data
psql -U postgres < dump.sql
^D
# if there are no errors, remove the backup
rm -rf ~/_data
Upgrade major version using pg_upgrade (example 11 to 12)
Init DB
/usr/pgsql-12/bin/initdb
Stop servers
systemctl stop postgresql-11 postgresql-12
Upgrade check (always run this first)
# --link links rather than copies files, without this option you need to make sure you have enough storage to copy the entire cluster
# using this option, however, means we CANNOT go back to the old version
# use -p OLDPORT and -P NEWPORT for non-default ports
/usr/pgsql-12/bin/pg_upgrade --old-bindir /usr/pgsql-11/bin --new-bindir /usr/pgsql-12/bin --old-datadir /var/lib/pgsql/11/data --new-datadir /var/lib/pgsql/12/data --link --check
Upgrade
# --link links rather than copies files, without this option you need to make sure you have enough storage to copy the entire cluster
# using this option, however, means we CANNOT go back to the old version
# use -p OLDPORT and -P NEWPORT for non-default ports
/usr/pgsql-12/bin/pg_upgrade --old-bindir /usr/pgsql-11/bin --new-bindir /usr/pgsql-12/bin --old-datadir /var/lib/pgsql/11/data --new-datadir /var/lib/pgsql/12/data --link
Vacuum DB
# you can run the ./analyze_new_cluster.sh script if you're using the default port, otherwise:
/usr/pgsql-12/bin/vacuumdb -p PORT --all --analyze-only