From bash: su postgres - switch user to postgres user psql -d db_name - log into a database SELECT * FROM some_table - perform a query OR psql -d db_name -c "SELECT * FROM some_table" - log into database and perform a query q to quit - exit out of query \q - exit to bash postgres session \dt - list table names exit - exit to original bash user pg_dump - backup a single database pg_dumpall - backup all databases Backup and Recovery: pg_dump -d scifi -f scifi.sql Secure copy the backup to another machine === scp scifi.sql some_user@some_ipaddress:~/scifi.sql Import database from backup === psql scifi < scifi.sql Change owner to new owner === alter table table_name owner to new_owner - repeat for all tables Backup Scripts Using gzip === pg_dump -d scifi | gzip > scifi_bak.gz Use native backup === pg_dump -Fc scifi > scifi_bak.bk Restore from native backup === pg_restore -d scifi scifi_bak.bk
Comments