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;
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';
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