sudo -u postgres psql dbname # typical root user
psql -h localhost -U user -W dbname # default host is unix-domain (not tcp to localhost)
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
pg_dump phisaver > file-to-write-to
# new server scp server:file-to-write-to .
Login to local server psql without specifying a database
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