Skip to content

Instantly share code, notes, and snippets.

@DominikSerafin
Last active November 10, 2021 18:37
Show Gist options
  • Save DominikSerafin/7f6c074428b94ebfaa7e9a2ef866a543 to your computer and use it in GitHub Desktop.
Save DominikSerafin/7f6c074428b94ebfaa7e9a2ef866a543 to your computer and use it in GitHub Desktop.
PostgreSQL Snippets

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;

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

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;

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment