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