03.12.2024

Cómo gestionar bases de datos en PostgreSQL

PostgreSQL, a menudo llamado “Postgres”, es un potente sistema de gestión de bases de datos relacionales de código abierto. La gestión de bases de datos en PostgreSQL implica tareas como la creación, modificación y eliminación de bases de datos, tablas y permisos de usuario. Esta guía le guiará a través de los comandos esenciales de gestión de bases de datos PostgreSQL.

1. Acceso a la línea de comandos de PostgreSQL

Para administrar bases de datos PostgreSQL, primero acceda a la interfaz de línea de comandos (CLI) de PostgreSQL. Por defecto, PostgreSQL utiliza la cuenta de usuario postgres.

Paso 1: Cambiar al usuario PostgreSQL

sudo -i -u postgres

Paso 2: Acceder a la CLI de PostgreSQL

psql

Una vez en la CLI, verás el prompt postgres=#, indicando que estás en el entorno PostgreSQL.

2. Creación de una nueva base de datos

Para crear una nueva base de datos, utilice el comando CREATE DATABASE. La sintaxis es la siguiente

CREATE DATABASE database_name;

Ejemplo:

CREATE DATABASE my_database;

Esto crea una base de datos llamada mi_base_de_datos. Para verificarlo, utilice:

\l

3. Creación y gestión de usuarios

Los usuarios de bases de datos necesitan permiso para acceder y modificar bases de datos. A continuación se explica cómo crear y gestionar usuarios en PostgreSQL.

Crear un nuevo usuario

CREATE USER username WITH PASSWORD 'password';

Ejemplo:

CREATE USER dbuser WITH PASSWORD 'securepassword';

Conceder acceso a la base de datos

Para permitir que un usuario acceda a una base de datos y la gestione, utilice el comando GRANT:

GRANT ALL PRIVILEGES ON DATABASE database_name TO username;

Ejemplo:

GRANT ALL PRIVILEGES ON DATABASE my_database TO dbuser;

Esto concede a dbuser acceso completo a mi_base_de_datos.

4. Conexión a una base de datos

Para conectarse a una base de datos específica, utilice el comando \c seguido del nombre de la base de datos.

\c mi_base_de_datos

El prompt cambiará para indicar la base de datos activa, permitiéndole gestionar tablas, insertar datos y realizar consultas.

5. Creación y gestión de tablas

Las tablas almacenan datos en un formato estructurado. A continuación se explica cómo crear, modificar y eliminar tablas en PostgreSQL.

Crear una tabla

Utilice el comando CREATE TABLE para definir una nueva tabla y sus columnas:

CREATE TABLE table_name ( column1 data_type PRIMARY KEY, column2 data_type, column3 data_type );

Ejemplo:

CREATE TABLE employees ( employee_id SERIAL PRIMARY KEY, name VARCHAR(100), department VARCHAR(50), salary NUMERIC );

Insertar Datos en una Tabla

Inserte registros usando la sentencia INSERT INTO:

INSERT INTO employees (name, department, salary) VALUES ('John Doe', 'HR', 50000);

Consultar datos

Recuperar datos de una tabla utilizando SELECT:

SELECT * FROM empleados;

Actualizar datos

Para actualizar los registros de una tabla:

UPDATE empleados SET salario = 55000 WHERE nombre = 'Juan Pérez';

Borrar datos

Para borrar filas específicas:

DELETE FROM empleados WHERE nombre = 'Juan Pérez';

6. Gestión del acceso y la seguridad de la base de datos

Por motivos de seguridad, conceda sólo los privilegios necesarios a los usuarios:

Revocar acceso:
REVOKE ALL PRIVILEGES ON DATABASE my_database FROM dbuser;
Restringir permisos de tabla:
GRANT SELECT ON TABLE empleados TO dbuser;

Esto permite a dbuser sólo ver los datos de la tabla empleados sin hacer cambios.

7. Copia de seguridad y restauración de bases de datos

PostgreSQL ofrece comandos para la copia de seguridad y restauración de bases de datos.

Copia de seguridad de una base de datos

Utilice el comando pg_dump para realizar una copia de seguridad de una base de datos en un archivo:

pg_dump my_database > my_database_backup.sql

Restaurar una base de datos

Utilice el comando psql para restaurar una base de datos a partir de un archivo de copia de seguridad:

psql my_database < my_database_backup.sql

8. Eliminación de bases de datos y tablas

Cuando una base de datos o tabla ya no sea necesaria, utilice el comando DROP para eliminarla.

Eliminar una tabla

DROP TABLE table_name;

Ejemplo:

DROP TABLE employees;

Eliminar una base de datos

Salga primero de la base de datos (si está conectada) y, a continuación, elimínela:

DROP DATABASE nombre_base_de_datos;

9. Salir de PostgreSQL

Para salir de la interfaz de línea de comandos de PostgreSQL, utilice:

\q

Conclusión

Administrar bases de datos en PostgreSQL implica crear y configurar bases de datos, configurar tablas, administrar usuarios y realizar copias de seguridad. Dominando estos comandos, usted puede administrar eficientemente bases de datos PostgreSQL, asegurando la integridad y seguridad de los datos.