Basic Postgresql Commands

Create user, database and grant roles

Drop Database :

DROP database john_db;

Create Database :

CREATE DATABASE john_db;

Create User :

CREATE USER john;

or,

CREATE USER john WITH ENCRYPTED PASSWORD 'john@password';

Alter User :

ALTER USER john WITH ENCRYPTED PASSWORD 'john@password';

Grant Privileges :

GRANT ALL PRIVILEGES ON DATABASE john_db TO john;

Grant Privileges of everything to postgres, if not given with:

grant all privileges on all tables in schema public to postgres;

grant all privileges on all sequences in schema public to postgres;

Note: A schema is a namespace that contains a collection of database objects, such as tables, views, and functions. The schema public is the default schema.

Query all the tables in database

SELECT    table_name
FROM      information_schema.tables
WHERE     table_schema = 'public'
ORDER BY  table_name;

---

Postgres Dumps

# Export database

$ pg_dump -U {{ db_user }} {{ db_name }} > `date +%Y-%m-%d-%H:%M:%S`.pgsql

# Import database

$ psql -U {{ db_user }} {{ db_name }} < db_backup_to_be_imported.pgsql

Unix Domain Socket

psql: error: could not connect to server: could not connect to server: No such file or directory
        Is the server running locally and accepting
        connections on Unix domain socket "/tmp/.s.PGSQL.5432"?

$ rm /usr/local/var/postgres/postmaster.pid
$ brew services restart postgresql

References:

• https://stackoverflow.com/questions/13410686/postgres-could-not-connect-to-server