14 Comandos para administrar Postgres

psql es un cliente CLI para Postgres que permite conectarse al servidor de BD, crear BD, crear usuarios, ejecutar consultas SQL y realizar tareas de administración de forma general.

Nota: Este procedimiento ha sido ejecutado sobre Debian 9.9 y Postgres 9.6

Vea la segunda parte: Comandos útiles para administrar PostgreSQL

En Debian tenemos las siguiente estructura de directorios para los ficheros de configuración de Postgres.

/etc/postgresql
└── 9.6
    └── main
        ├── environment
        ├── pg_ctl.conf
        ├── pg_hba.conf
        ├── pg_ident.conf
        ├── postgresql.conf
        └── start.conf

1. Conectarse a Postgres con el usuario admin o root

$ sudo -u postgres psql

2. Listar BD

\l

3. Usar/Seleccionar una base datos

\c dbname;

4. Listar tablas

\dt

5. Crear base datos

CREATE DATABASE dbname OWNER username;

Cuando creamos la BD dbname se crea el schema public con propietario postgres por defecto, entonces debemos cambiarlo a username con (antes debemos seleccionar la BD dbname):

ALTER SCHEMA public OWNER TO username;

6. Eliminar base datos

DROP DATABASE dbname;

7. Importar una base datos

$ psql username  -h hostname -d dbname < dump.sql

8. Crear usuario

CREATE USER username WITH PASSWORD 'MYPASS';

9. Conectarse a la base datos con el usuario creado

$ psql -U usuario -h 127.0.0.1 -d basedatos

Para conectarnos a la base datos desde un dispositivo diferente al servidor de base datos debemos modificar los ficheros: postgresql.conf y pg_hba.conf.

En el fichero postgresql.conf buscamos la directiva listen_address y agregamos las direcciones IPs en las cuales el servidor de base datos va a recibir conexiones, por ejemplo:

listen_addresses = 'localhost, 192.168.100.29'

El fichero pg_hba.conf también controla el acceso al servidor Postgres, buscamos la sección: IPv4 local connections y agregamos nuestra regla, por ejemplo:

# TYPE  DATABASE        USER            ADDRESS             METHOD
host    all             all         192.168.100.1/24        md5

Con la regla anterior establecemos que cualquier usuario se puede conectar a cualquier base de datos desde nuestra red de area local: 192.168.100.1/24, si queremos restringir el acceso a un solo usuario y a una base datos lo podemos hacer de la siguiente manera:

host    basedatos             usuario         192.168.100.1/24        md5

10. Establecer super privelegios

ALTER USER username WITH SUPERUSER;

11. Listar usuarios

\du

12. Eliminar usuario

DROP USER nomusr

13. Obteniendo ayuda

\?

\h

14. Salir de la consola de Postgres

\q

Lectura recomendada

– man psql