Guia de Desarrollo Express para Postgresql

guia-postgres.md

Guia de Desarrollo Express para Postgresql

Esta es una guia práctica para varios casos de uso común al momento de desarrollar usando postgresql tanto en producción como en desarrollo.

Instalación

Instalación en Linux

ramses@debian:~$ sudo apt-get install postgresql postgresql-contrib libpq-dev

Versión instalada Postgresql

ramses@debian:~$ psql --version
psql (PostgreSQL) 9.4.10

ramses@debian:~$ psql -V
psql (PostgreSQL) 9.4.10

Cliente de Postgres

Para poder desarrollar los siguientes comandos es necesario tener disponible una sesión cliente en un servidor PostgreSQL. Lo veremos en el siguiente comando:

Iniciar el cliente de Postgres

Este es un caso sencillo, asumiendo que inciamos el cliente de postgres con permisos root, por lo tanto no se requiere el uso de una contreseña.

ramses@debian:~$ sudo su postgres
postgres@debian:~$ psql
psql (9.4.10)
Type "help" for help.

postgres=#

Salir del cliente de Postgres

postgres=# \q
ramses@debian:~$

Gestion de las Bases de datos

Los siguientes comandos son ejecutados dentro del cliente de Postgresql.

Crear una nueva base de datos

CREATE DATABASE db_test;
CREATE DATABASE

Borrar una base de datos

DROP DATABASE db_test;
DROP DATABASE

Crear backups de una base de datos

Podemos respaldar nuesra base de datos ya sea con extensión .sql o con extensión .backup. Cualquiera que elijas te servirá después para recuperar la información de tu base de datos.

Crear respaldo .backup

Podemos crear un dump de nuestra base de datos con el siguiente código

ramses@debian:~$ sudo su postgres
postgres@debian:~$ pg_dump -i -h localhost -p 5432 -U postgres -F c -b -v -f /tmp/db_test.backup db_test

Ahora explicamos las banderas utilizadas

• pg_dump es el comando en si mismo.
• -i le indica que ignore la versión (entre el comando y la base de datos).
• -h localhost especifica el host del motor de base de datos (localhost en este caso).
• -p 5432 es la indicación del puerto donde corre el servicio.
• -U postgres es para indicarle que usaremos el usuario postgres.
• -F indica el formato de salida.
• c es la elección del formato (comprime en este caso).
• -b indica que vuelque los LO (large objects).
• -v verbose.
• -f es el archivo de salida.
• /tmp/db_test.backup es el nombre para el archivo de salida (con path).
• db_test es el nombre de la base a la que se le creará el respaldo.

Aquí podemos usar un atajo, para no escribir muchas banderas y usar los valores por defecto de postgres al ejecutar el siguiente comando:

ramses@debian:~$ sudo su postgres
postgres@debian:~$ pg_dump -f /tmp/db_test.backup db_test

Crear respaldo .sql

ramses@debian:~$ sudo su postgres
postgres@debian:~$ pg_dump db_test > /tmp/db_test.sql

¿Sencillo verdad?

Restaurar Backups

Al igual que los backups, puedes trabajar sobre archivos .sql o arhivos .backup. Personalmente prefiero trabajar con .sql ya que me han dado menos problemas.

Para usar estos comando, debemos estar totalmente seguros que la base de datos que queramos restaurar esté totalmente vacia.

Restaurar a partir del comando pg_restore (Para .backup)

ramses@debian:~$ sudo su postgres
postgres@debian:~$ pg_restore -i -h localhost -U postgres -d mibase -v "/tmp/db_test.backup"

Una vez que lo ejecutemos, se nos pedirá la contraseña del usuario que vamos a usar. Sobre los parámetros del ejemplo, paso a detallarlos.

• -i le indica que ignore la versión (entre el comando y la base de datos).
• -h localhost es el host de nuestro PostgreSql.
• -p 5432 es la indicación del puerto donde corre el servicio.
• -U postgres especifica que se usará el usuario postgres para la operación.
• -d mibase es para que realize la restauración sobre una base de datos en particular, en este caso mibase.
• -v ejecutará el comando en modo verbose (así podremos ir viendo la salida de cada paso del proceso). /tmp/db_test.backup es el archivo que usaremos como backup y que queremos ingresar.

Se puede simplificar el comando de la siguiente manera:

ramses@debian:~$ sudo su postgres
postgres@debian:~$ pg_restore -d db_test /tmp/db_test.backup

Nota: En este ejemplo se omitieron las comillas dobles, ya que la ruta hacie el archivo db_test.backup no contiene espacios, de lo contrario se usan las comillas dobles para que el interprete pueda encontrar el archivo sin problemas.

Restaurar a partir del comando psql (Para .sql)

ramses@debian:~$ sudo su postgres
postgres@debian:~$ psql db_test < /home/ramses/Documents/backups/db_test.sql

Manejo de Usuarios

Crear nuevo usuario

Los siguientes comandos son ejecutados dentro del cliente de Postgresql.

CREATE USER developer WITH PASSWORD 'super_secure_password';
CREATE ROLE

Asignar un usuario a una base de datos

GRANT ALL PRIVILEGES ON DATABASE db_test TO developer;
GRANT

Remover privilegios de un usuario de una base de datos

1. Primero debemos acceder al cliente de postgres indicando la base de datos que queremos modificar.

ramses@debian:~$ psql -d db_test;
psql (9.4.10)
Type "help" for help.
db_test=#

2. Ahora removemos los privilegios del usuario deseado

db_test=# DROP OWNED BY developer;
DROP OWNED

Listar todos los usuarios

SELECT * FROM pg_user;
                                    List of databases
  usename  | usesysid | usecreatedb | usesuper | usecatupd | userepl |  passwd  | valuntil | useconfig 
-----------+----------+-------------+----------+-----------+---------+----------+----------+-----------
 postgres  |       10 | t           | t        | t         | t       | ******** |          | 

Eliminar un usuario

Antes de eliminar un usuario, debemos estar seguros de que ha sido removido de todas las bases de datos donde tiene privilegios asignados.

DROP USER developer;
DROP ROLE

Si el usuario aún tiene privilegios en otras bases de datos, aparecerá un error mostrando las bases de datos donde aún tiene privilegios el usuario. En ese caso debemos remover sus privilegios accediendo a cada base de datos.