PostgreSQL¶

Access PSQL¶

sudo -s
su - postgres
psql

Quit¶

\q

List databases¶

\l

Connect to database¶

\c dbname
\connect dbname

Basic SQL Operations¶

Create User¶

CREATE USER username WITH PASSWORD 'password';

Create Database¶

CREATE DATABASE dbname;

Grant¶

GRANT ALL PRIVILEGES ON DATABASE dbname to username;

Check the size of all the databases¶

SELECT
    pg_database.datname,
    pg_size_pretty(pg_database_size(pg_database.datname)) AS size
    FROM pg_database;

Create new database and grant privileges¶

See also: Linux level commands -> Create Database with Owner set and Dump and Restore -> Restore

CREATE USER foobar WITH PASSWORD 'abc123';

GRANT ALL PRIVILEGES ON DATABASE "foobar" TO foobar;

Drop Database¶

drop database "example-website";

Linux level Commands¶

Create Database with Owner set¶

createdb foobar -O foobar

Dump and Restore¶

Dump¶

Restore¶

$ sudo -s
# su - postgres
-su-4.3$ psql
postgres=# drop database "example-website";
DROP DATABASE
postgres=# \q
-su-4.3$ createdb example-website -O example-website
-su-4.3$ pg_restore --clean --create -Fc -h localhost -O -d example-website -U example-website /home/user/example-website_20160204-221001.dmp

dropdb example-website
createdb -O example-website -E UTF8 example-website
pg_restore -Fc -h localhost -O -d example-website -U example-website /home/user/example-website-2015-12-21-220103.pgdump
-su-4.3$ pg_restore --clean --create -Fc -d nextbox -U netbox /home/user/netbox_20160204-221001.dmp