Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- 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
Advertisement
Add Comment
Please, Sign In to add comment