Postgres Cheatsheet#

A quick reference for quick and dirty postgres commands

Change databases#

When you connect to postgres it is always on a specific db

to change databases, close the old connection and reconnect on the new database:

\c name_database

Copy data from table in one schema to another

insert into schema2.the_table
select * 
from schema1.the_table;

View schemas in a db#

select nspname from pg_catalog.pg_namespace;

Alter or change the public schema name

alter schema public rename to original_public;
create schema public;

Add superuser privilege to a user

In psql:

ALTER USER my_user WITH SUPERUSER;

List all databases

In psql:

\l

View the users of a postgres instance

In psql:

\du

View the scemas in a postgres database

In psql:

\dn

Reset a postgres user password

In psql:

ALTER USER user_name WITH PASSWORD 'new_password';

Postgres connection strings

Dump (Backup) and Restore#

Dump a remote database

pg_dump -U <username> -h <ip> -p 5432 <database_name> > <backup_name>.bak

Create a database and user for access to it:

sudo -u postgres psql
create database <db_name>;
create user <username> with encrypted password '<password>';
grant all privileges on database <db_name> to <username>;

Restore the database:

sudo -u postgres psql <db_name> < <backup_name>.bak

PSQL Cheatsheet#

Select a database

\c <db_name>

list tables

\dt