Postgres

Create User/Role

1
create user user1 with password 'sUperSecreT';

List Users

1
\du

List databases

1
\l

Connect to/Switch database from inside psql

\c dbname

This is equivalent to use dbname in mysql

Assign previleges

Make user1 God !!

1
alter role user1 with Superuser;

But more realistically, we should give this user all the previleges only for specific database

1
GRANT ALL PRIVILEGES ON DATABASE testapp_dev to user1;

Create Database

As a user with correct previleges

1
CREATE DATABASE db1 OWNER user1;

Describe a Table

If you have used mysql in past, then this is similar to desc tablename

\d+ tablename;

Backup

pg_dump -U username dbname -Fc > /path/to/db.custom.format.dump

This option :

  • uses compression by default
  • can be used by pg_restore

Restore

$ date;pg_restore -U username -d dbname /path/to/db.custom.format.dump > /tmp/restore.log 2>&1;date

Adding date at the beginning and end gives you rough idea of how long it took.

Automated password

  • Use .pgpass file. See details here
  • File format: hostname:port:database:username:password
  • Permissions: chmod 0600 ~/.pgpass

Export the query output to CSV

psql -U username DBname -F , --no-align -c "select * from table" > /path/to/file.csv

Extensions

See list of extensions already installed:

1
2
3
4
5
6
=> \dx
                 List of installed extensions
  Name   | Version |   Schema   |         Description
---------+---------+------------+------------------------------
 plpgsql | 1.0     | pg_catalog | PL/pgSQL procedural language
(1 row)

To install an extension:

1
2
3
4
5
6
7
8
9
=> CREATE EXTENSION IF NOT EXISTS "pgcrypto";
CREATE EXTENSION
=> \dx
                  List of installed extensions
   Name   | Version |   Schema   |         Description
----------+---------+------------+------------------------------
 pgcrypto | 1.2     | public     | cryptographic functions
 plpgsql  | 1.0     | pg_catalog | PL/pgSQL procedural language
(2 rows)