PostgreSQL Snippets

01PostgreSQLSnippets.md

Notes

• 'postgres' user is superuser

Resources

• PostgreSQL command line cheatsheet
• Hyperpolyglot Cheatsheet

02guides.md

Database Export and Import

1. pg_dump -h localhost -U username dbname -w > "/backups/dbname-$(date +'%y-%m-%d-%H%M').backup"
2. su - postgres
3. psql --set ON_ERROR_STOP=on --single-transaction db_name < backup-file
4. grant all permissions to the db for the db user
   • GRANT ALL ON ALL TABLES IN SCHEMA public to username;
   • GRANT ALL ON ALL SEQUENCES IN SCHEMA public to username;
   • GRANT ALL ON ALL FUNCTIONS IN SCHEMA public to username;

03psql-pg_dump-etc.md

Bash Commands

Login into psql sudo -u postgres psql

Login into postgres user su - postgres

Create database user createuser -P username

Delete database user dropuser username

Create database createdb dbname

Drop database dropdb dbname

Execute PSQL Command psql -c <somecommand>

Import Database psql -h localhost -U username db_name < /srv/db_name.backup

Import Database #2 psql -h localhost -U username -d dbname -f file.sql

Import Database (from custom format, atomic) pg_restore -U username -d dbname -1 filename

Dump Database (export) pg_dump -h localhost -U username dbname > /srv/db_name.backup

Dump Database (custom compressed format) pg_dump -Fc -h localhost -U username dbname > /srv/db_name.backup

Dump Database (one line with password) PGPASSWORD=abc123 pg_dump -h localhost -U username dbname > /srv/db_name.backup

04queries.md

Queries and commands inside PSQL

General Help \?

Syntax Help on SQL Commands \help <somequery>

List Databases \l or \list

List users \du or \dg

Select database \c db or \connect dbname

See currently selected database \c or \connect

Import database \i <file>

Quit PSQL \q or CTRL+D

Create user CREATE USER username WITH PASSWORD 'password';

Drop User DROP USER username;

Create Database with owner CREATE DATABASE dbname WITH OWNER username;

Change user password ALTER USER postgres PASSWORD 'new_password';

Give privileges to database to user GRANT ALL PRIVILEGES ON DATABASE dbname TO username;

Drop Database DROP DATABASE dbname;

05schema-exploring.md

Describe current schema

db info schema

SET search_path TO 'information_schema';

list tables

-- list the tables in the schema
SELECT table_name from information_schema.tables
WHERE table_schema = 'myschema';

describe table

SELECT column_name, is_nullable, data_type, character_maximum_length
FROM information_schema.columns
WHERE table_name = 'mytable'
ORDER BY ordinal_position