PostgreSQL

Conocer la versión:

1
SHOW server_version;

Listar las bases de datos:

1
2
3
4
5
\l  -- List
\l+ -- List with extra (include size)
\list
SELECT datname FROM pg_database;
SELECT current_database();

Listar los esquemas:

1
2
\dn
\dn+

Cambiar o seleccionar una base de datos:

\c demo
\connect demo

Listar las tablas:

1
2
3
\dt
\dt+
SELECT * FROM pg_catalog.pg_tables;

Conocer el tamaño de la base de datos o de una tabla:

1
2
3
4
5
-- Database Size:
SELECT pg_size_pretty(pg_database_size('db_demo'));

-- Table Size:
SELECT pg_size_pretty(pg_relation_size('table_demo'));

Listar las conexiones activas:

1
SELECT * FROM pg_stat_activity;

Listar las conexiones por base de datos:

1
SELECT datname, COUNT(datid) FROM pg_stat_activity GROUP BY datname;

Listar los usuarios (roles):

1
2
3
4
5
\du
\du+
SELECT rolname, rolcanlogin, rolsuper, rolcreatedb, rolcreaterole
FROM pg_roles
ORDER BY rolname;

Ver el propietario de la base de datos

1
2
SELECT datname, pg_get_userbyid(datdba) AS owner, datcollate, datctype
FROM pg_database;

Conocer la configuración:

1
SELECT setting::int max_conn FROM pg_settings WHERE name=$$max_connections$$;

Usario para una aplicación:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
CREATE ROLE app_owner
  LOGIN
  PASSWORD 'password_owner'
  NOSUPERUSER
  CREATEDB
  NOCREATEROLE;

CREATE DATABASE app_db
  OWNER app_owner
  ENCODING 'UTF8';

GRANT CONNECT ON DATABASE app_db TO app_user;

-- CREATE SCHEMA app_schema AUTHORIZATION app_owner;
GRANT USAGE, CREATE ON SCHEMA public TO app_user;
GRANT USAGE, SELECT, UPDATE ON ALL SEQUENCES IN SCHEMA public TO app_user;

Hacer un respaldo:

1
2
3
4
5
6
7
8
# Forma tradicional:
pg_dump -h 127.0.0.1 -p 5432 -d app_db -U app_user --no-owner --no-privileges > app.sql
# Tarda mucho, y comparado con gzip no es mucho:
pg_dump -h 127.0.0.1 -p 5432 -d app_db -U app_user -Fc -Z9 -f app.dump
# En paralelo, usando cuatro cores:
pg_dump -h 127.0.0.1 -p 5432 -d app_db -U app_user -Fd -j 4 -f app_dir
# Es la mejor opción usar gzip:
pg_dump -h 127.0.0.1 -p 5432 -d app_db -U app_user | gzip > app.sql.gz

Hacer un restore:

1
gunzip -c app.sql.gz | psql -h 127.0.0.1 -p 5432 -U app_user -d app_db

Hacer un respaldo de los roles:

1
pg_dumpall --roles-only > roles.sql