PostgreSQL notes

Linux - change to postgres user in command line interface

  # sudo su - postgres

Show the location of the database files

  postgres@SERVER:~$ psql
  postgres=# SELECT setting FROM pg_settings WHERE name = 'data_directory';

Backup database

  # pg_dump db_name > file_name

Restore pg_dump file

  # psql db_name < file_name

Some table modification commands

  ALTER TABLE some_table_name RENAME "row.names" TO id;
  ALTER TABLE some_table_name ALTER COLUMN id TYPE integer USING id::integer;
  ALTER TABLE some_table_name ADD PRIMARY KEY (id);

list databases

  postgres # psql
  postgres=# \l

dump from one host to another

  pg_dump -h host1 dbname | psql -h host2 dbname

Reload with

  psql dbname < archive.sql

Create a database

  createdb dbname

Using a zip archive with backup and restore

  pg_dump dbname | gzip > filename.gz
  gunzip -c filename.gz | psql dbname

remote backup

  pg_dump -U stevet -h 192.168.1.9 -p 5432 cipher > 20220224_cipher.sql

remote restore

  psql -U stevet -h 192.168.1.9 -p 5432 cipher < 20220224_cipher.sql

Changing user privileges

  ALTER USER user_name WITH SUPERUSER/NOSUPERUSER;

To dump all databases:

  pg_dumpall > db.out

To restore database(s) from this file, you can use:

  psql -f db.out postgres