Notas sobre PostgreSQL

Para hacer login al servidor PostgreSQL se ejecutan dos comandos:

  • $ sudo su - postgres es útil para loguearse como el usuario postgres
  • Después de eso con el comando psql podemos hacer login al prompt de PostgreSQL (el servidor)

psql -U user -d database -h 127.0.0.1 -W.

Roles

PostgreSQL administra los permisos bajo el concepto de «roles».

Crear roles (dentro del servidor PostgreSQL): # create role developer;. Un método alternativo para crear roles es usar el comando createuser en el prompt PostgreSQL.

Listar los roles \du. Eliminar roles se hace de la siguiente forma: # drop role developer;.

Privilegios

Los roles tienen privilegios. Por ejemplo, cuando un rol no tiene permiso para loguearse se lo podemos otorgar así: alter role developer with login o así alter user developer CREATEDB.

De la misma forma otorgamos todos los permisos a un rol así: GRANT ALL PRIVILEGES ON DATABASE database to usuario;

Obtener permisos de administrador

Para acceder directamente desde el prompt de tu usuario, hay que crear un rol y una base de datos que tengan el mismo nombre de tal usuario.

Suponiendo que el nombre de tu usuario es hariseldon, Hacemos lo siguiente: desde el prompt del usuario postgres creamos un rol createuser hariseldon. Aparecerá un prompt para que indiquemos que queremos que este nuevo rol tenga privilegios de administrador. Después basta crear una base de datos con el nombre hariseldon: create database hariseldon.

Resetear contraseña

alter user postgres with password ''

Mostrar tablas en la base de datos

\dt

\d+ table es el equivalente a describe table de MySQL.

El equivalente a use database de MySQL es \c database.

Respaldar y restaurar

Otra de las funciones que nos conviene saber es cómo respaldar y restaurar bases de datos.

Respaldar: pg_dump -U usuario nombredb -h localhost -f respaldodb.sql.

Antes de restaurar hay que asegurarse de que la base de datos ha sido creada: $ psql -U usuario -d database -f script.sql.

Otros tips

Darle la propiedad de una base de datos a determinado rol:

ALTER DATABASE name OWNER TO developer;

Heredar los privilegios de otro rol:

GRANT admin TO joe;

Ejecutar instrucciones SQL desde un archivo:

psql -d myDataBase -a -f myInsertFile

Algunas consultas útiles

  • select datname from pg_database: Esta consulta nos da una lista de las bases de datos que existen, me gusta más que la salida de \l.
  • select * from pg_stat_activity: Esta consulta nos da una lista de las bases de datos con una sesión abierta.

Total de filas en cada tabla de la base de datos a la que estás logueado:

SELECT schemaname,relname,n_live_tup
FROM pg_stat_user_tables
ORDER BY n_live_tup DESC;

8 comentarios sobre “Notas sobre PostgreSQL”

  3. Hola, vi tu post y me interesa saber si conoces alguna manera de restaurar una base de datos en postgresql sin usar la consola. o sea, por dentro de la aplicacion web tengo un boton donde necesito mandar a ejecutar el script para q me realice una restaura completa de la base de datos, esto significa con el usuario autenticado en el sistema. no he encontrado un post donde diga como hacerlo desde dentro, no se puede hacer asi???

    1. No sé qué framework usas, pero todos los lenguajes de programación tienen herramientas para ejecutar desde la aplicación web instrucciones en la línea de comandos. Por ejemplo, en Python, se llama «subprocess».

      2. Uso codeigniter v3 y logro hacer la restaura de todas las tablas excepto las de usuario roles sesiones y trazas y me dijeron q la restaura debe ser completa. No parece haber manera de hacerlo con postgre por favor si sabes la manera respondeme. Necesito restaurar todo y si postgre no deja restaurar todas las tablas con el usuario autenticado saber q no se puede y poder defenderme el día de la discusión de mi tesis no vaya a ser q diga q no se puede y si exista la forma de hacerlo!!

Deja un comentario