Postgres Tricks

Log-the-frick-in

• sudo -u postgres psql dbname # typical root user
• psql -h localhost -U user -W dbname # default host is unix-domain (not tcp to localhost)

More detail here.

Create read-only user

# login to the database!
sudo -u postgres sql phisaver

# from psql>
ALTER DEFAULT PRIVILEGES IN SCHEMA public grant select on tables to phisaver;

Backup and restore

Swap ‘phisaver’ for database name of your choice.

Backup the data to a file and transfer it

# current-serverpg_dump phisaver > file-to-write-to 

# new server
scp server:file-to-write-to .

Login to local server psql without specifying a database

# new-server
sudo -u postgres psql 

See docs if there are active connections.

Remove any existing data locally. Is there are better options / way to achieve this (see pg_dump options)?

# new server psql
drop database phisaver;create database phisaver;

And load up the data:

sudo -u postgres phisaver < file-you-wrote-to