Iñigo GonzálezIT Operations · Infra

GuíasGuides · Nivel 3Level 3

PostgreSQL, MySQL y SQL Server: las tareas de operaciones, de la copia a la consulta lentaPostgreSQL, MySQL and SQL Server: the operations tasks, from backup to slow query

Lo que te piden de una base de datos sin ser DBA: conectar y ver qué hay, copia y restauración probada, espacio y crecimiento, altas y bajas de usuarios y qué consulta está bloqueando la aplicación, en los tres motores a la vez.What you are asked to do with a database without being a DBA: connect and see what is there, tested backup and restore, disk space and growth, user joiners and leavers, and which query is blocking the application, across the three engines side by side.

Nivel 3Level 3 · PostgreSQL, MySQL y SQL ServerPostgreSQL, MySQL and SQL Server · 7 min7 min read · ActualizadoUpdated 22 oct 202622 Oct 2026

Qué vas a conseguir

Resolver el 80 % de los tickets de base de datos que llegan a operaciones sin esperar al DBA: "la aplicación va lenta", "hay que dar acceso a X", "¿tenemos copia de ayer?", "el disco del servidor de base de datos está al 92 %". Los tres motores, el mismo gesto, con el comando de cada uno.

Antes de empezar

  • Acceso al servidor de cada motor (SSH o RDP) y una cuenta con permisos de administración de la base de datos, usada solo para estas tareas y con la contraseña en el gestor del equipo.
  • Los clientes: psql, mysql y sqlcmd (en Windows viene con SQL Server; en Linux, el paquete mssql-tools18).
  • Un directorio de copias con espacio igual al tamaño de la base de datos más grande, y el ticket de a quién avisar antes de restaurar nada.

1. Conectar y ver qué hay

bash · srv-db-01
psql -h localhost -U postgres -c "\l+" | awk -F'|' 'NR>3 {print $1, $7}' | head -5 appdb        | 2412 MB postgres     | 7501 kBmysql -u root -p -e "SELECT table_schema AS db, ROUND(SUM(data_length+index_length)/1024/1024) AS mb FROM information_schema.tables GROUP BY table_schema ORDER BY mb DESC;"+--------------------+------+| db                 | mb   |+--------------------+------+| intranet           | 1870 || mysql              |    3 |+--------------------+------+sqlcmd -S srv-sql-01 -E -Q "SELECT name, (SELECT SUM(size)*8/1024 FROM sys.master_files mf WHERE mf.database_id=d.database_id) AS mb FROM sys.databases d ORDER BY mb DESC" -Wname    mbERP     38210tempdb  4096master  7

Antes de tocar nada: qué bases de datos hay, cuánto ocupan y, en el ticket, cuál es la de la aplicación que falla. -E en sqlcmd usa tu cuenta de Windows; en PostgreSQL y MySQL la cuenta de administración va en ~/.pgpass y ~/.my.cnf con permisos 600, nunca en la línea de comandos de un script.

2. Copia y restauración (probada)

bash · srv-db-01
# PostgreSQL: copia lógica comprimida y restauración en una base de datos de pruebapg_dump -h localhost -U postgres -Fc appdb -f /backup/appdb-$(date +%F).dumpcreatedb -h localhost -U postgres appdb_restore_test && pg_restore -h localhost -U postgres -d appdb_restore_test -j 4 /backup/appdb-$(date +%F).dumppsql -h localhost -U postgres -d appdb_restore_test -tAc "SELECT count(*) FROM orders;"184233# MySQLmysqldump --single-transaction --routines --triggers intranet | gzip > /backup/intranet-$(date +%F).sql.gzmysql -e "CREATE DATABASE intranet_restore_test" && zcat /backup/intranet-$(date +%F).sql.gz | mysql intranet_restore_test
sqlcmd · srv-sql-01
sqlcmd -S srv-sql-01 -E -Q "BACKUP DATABASE ERP TO DISK='E:\Backup\ERP_2026-10-22.bak' WITH COMPRESSION, CHECKSUM, STATS=10"10 percent processed. ... 100 percent processed.BACKUP DATABASE successfully processed 4891264 pages in 98.412 seconds (388.3 MB/sec).sqlcmd -S srv-sql-01 -E -Q "RESTORE VERIFYONLY FROM DISK='E:\Backup\ERP_2026-10-22.bak' WITH CHECKSUM"The backup set on file 1 is valid.sqlcmd -S srv-sql-01 -E -Q "RESTORE DATABASE ERP_restore_test FROM DISK='E:\Backup\ERP_2026-10-22.bak' WITH MOVE 'ERP' TO 'E:\Restore\ERP_test.mdf', MOVE 'ERP_log' TO 'E:\Restore\ERP_test.ldf', STATS=25"

Una copia que no se ha restaurado nunca es una esperanza, no una copia. La restauración de prueba a otra base de datos y un count(*) de la tabla principal es lo que apuntas en el ticket mensual de copias (y lo que el repo 33 de mi GitHub, backup-restore-test-tracker, sigue en el tiempo). Si el servidor ya tiene Veeam o un agente de copias (Nivel 2), estos comandos son la verificación, no el sustituto. Borra las bases de datos *_restore_test al terminar.

3. Espacio y crecimiento

bash · srv-db-01
psql -h localhost -U postgres -d appdb -c "SELECT relname, pg_size_pretty(pg_total_relation_size(oid)) AS size, n_dead_tup FROM pg_stat_user_tables t JOIN pg_class c ON c.oid=t.relid ORDER BY pg_total_relation_size(oid) DESC LIMIT 3;"   relname    |  size   | n_dead_tup--------------+---------+------------ audit_log    | 1412 MB |    2203110 orders       |  611 MB |       8120psql -h localhost -U postgres -d appdb -c "VACUUM (VERBOSE, ANALYZE) audit_log;" 2>&1 | tail -1du -sh /var/lib/pgsql/16/data/pg_wal3.1G    /var/lib/pgsql/16/data/pg_wal
sqlcmd · srv-sql-01
sqlcmd -S srv-sql-01 -E -d ERP -Q "SELECT name, size*8/1024 AS mb, CAST(FILEPROPERTY(name,'SpaceUsed') AS int)*8/1024 AS used_mb FROM sys.database_files" -Wname     mb     used_mbERP      36120  29877ERP_log  2090   1912sqlcmd -S srv-sql-01 -E -Q "SELECT name, recovery_model_desc, log_reuse_wait_desc FROM sys.databases WHERE name='ERP'" -Wname  recovery_model_desc  log_reuse_wait_descERP   FULL                 LOG_BACKUP

Los tres culpables habituales del disco al 92 %: en PostgreSQL, una tabla con millones de tuplas muertas sin VACUUM (y pg_wal creciendo porque un slot de replicación quedó huérfano); en MySQL, los binlogs sin expire_logs_days/binlog_expire_logs_seconds; en SQL Server, el log de transacciones en modo FULL sin copias de log: log_reuse_wait_desc = LOG_BACKUP lo dice, y la solución es programar BACKUP LOG, no SHRINKFILE cada semana.

4. Altas y bajas de usuarios

bash · srv-db-01
# PostgreSQL: rol de solo lectura para el analistapsql -h localhost -U postgres -d appdb -c "CREATE ROLE laura_prado LOGIN PASSWORD 'cambiar-en-el-primer-login'; GRANT CONNECT ON DATABASE appdb TO laura_prado; GRANT USAGE ON SCHEMA public TO laura_prado; GRANT SELECT ON ALL TABLES IN SCHEMA public TO laura_prado; ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO laura_prado;"# MySQLmysql -e "CREATE USER 'laura_prado'@'10.20.%' IDENTIFIED BY 'cambiar-en-el-primer-login' PASSWORD EXPIRE; GRANT SELECT ON intranet.* TO 'laura_prado'@'10.20.%';"# baja: revocar y, si no tiene objetos, eliminarpsql -h localhost -U postgres -d appdb -c "REASSIGN OWNED BY j_navarro TO postgres; DROP OWNED BY j_navarro; DROP ROLE j_navarro;"
sqlcmd · srv-sql-01
sqlcmd -S srv-sql-01 -E -Q "CREATE LOGIN [CONTOSO\sg-erp-readonly] FROM WINDOWS; USE ERP; CREATE USER [CONTOSO\sg-erp-readonly] FOR LOGIN [CONTOSO\sg-erp-readonly]; ALTER ROLE db_datareader ADD MEMBER [CONTOSO\sg-erp-readonly];"

La regla que vale para los tres: grupo, no persona, siempre que el motor lo permita. SQL Server con un grupo de AD es el ideal: la baja se hace en Active Directory (Nivel 1) y no hay que tocar la base de datos. En PostgreSQL y MySQL, un rol de lectura con GRANT a usuarios nominales, y la lista de usuarios en la revisión trimestral de accesos.

5. "La aplicación va lenta": qué está pasando ahora

bash · srv-db-01
psql -h localhost -U postgres -d appdb -c "SELECT pid, now()-query_start AS runtime, state, wait_event_type, left(query,60) AS query FROM pg_stat_activity WHERE state <> 'idle' AND pid <> pg_backend_pid() ORDER BY runtime DESC LIMIT 5;"  pid  |     runtime     | state  | wait_event_type |                   query-------+-----------------+--------+-----------------+---------------------------------------------- 18211 | 00:14:02.118    | active | Lock            | UPDATE orders SET status='X' WHERE customer_id=… 18240 | 00:13:58.004    | active | Lock            | SELECT * FROM orders WHERE customer_id=… 17990 | 00:14:40.771    | idle in transaction |  | BEGIN; UPDATE orders SET …psql -h localhost -U postgres -d appdb -c "SELECT pg_terminate_backend(17990);"mysql -e "SELECT id, user, time, state, LEFT(info,60) FROM information_schema.processlist WHERE command <> 'Sleep' ORDER BY time DESC LIMIT 5;"
sqlcmd · srv-sql-01
sqlcmd -S srv-sql-01 -E -Q "SELECT r.session_id, r.blocking_session_id, r.wait_type, r.wait_time/1000 AS wait_s, LEFT(t.text,60) AS sql_text FROM sys.dm_exec_requests r CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t WHERE r.session_id > 50 ORDER BY r.wait_time DESC" -Wsession_id  blocking_session_id  wait_type  wait_s  sql_text71          58                   LCK_M_U    812     UPDATE Invoices SET Status = …58          0                    NULL       0       (sesión inactiva con transacción abierta)

El patrón es idéntico en los tres: una sesión inactiva con una transacción abierta (alguien abrió una herramienta, hizo un BEGIN y se fue a comer) bloquea a todas las demás. pg_stat_activity, processlist y dm_exec_requests te dan el pid de quién bloquea; matar esa sesión (pg_terminate_backend, KILL id, KILL session_id) devuelve la aplicación a la vida, y el ticket al desarrollador dice qué consulta y desde qué cliente. Si no hay bloqueos y es una consulta pesada, EXPLAIN ANALYZE en PostgreSQL/MySQL o el plan de ejecución en SSMS es el siguiente paso, y ese sí es del DBA o del desarrollador.

6. Lo que revisas cada mes

  • Restauración de prueba de cada motor, con el count(*) apuntado.
  • Espacio: tablas que más crecen, pg_wal/binlogs/log de transacciones, y la previsión a seis meses con la tendencia.
  • Usuarios: cuentas nominales que ya no están en Active Directory; contraseñas de las cuentas de aplicación en el gestor y con fecha.
  • Parches del motor: PostgreSQL y MySQL con el parcheo mensual de Ansible; SQL Server con su CU trimestral en la ventana de cambios, con copia antes.

Siguientes pasos

  • Monitorización: postgres_exporter, mysqld_exporter y el de SQL Server en el Prometheus de la guía de Grafana; el panel de "sesiones bloqueadas > 0 durante 5 minutos" te avisa antes que el usuario.
  • Alta disponibilidad: réplica en streaming (PostgreSQL), replicación (MySQL) y Always On (SQL Server) cuando el negocio no acepte una hora de parada; antes de eso, la restauración probada ya cubre lo importante.
  • Autenticación integrada en PostgreSQL con Kerberos (guía de Linux + AD): la misma baja de AD cierra también la base de datos.

¿Falta algo o en tu entorno es distinto? Escríbeme y lo corrijo.

What you'll get

Solve 80% of the database tickets that reach operations without waiting for the DBA: "the application is slow", "we need to give X access", "do we have yesterday's backup?", "the database server disk is at 92%". Three engines, the same move, with each one's command.

Before you start

  • Access to each engine's server (SSH or RDP) and an account with database administration rights, used only for these tasks and with its password in the team's password manager.
  • The clients: psql, mysql and sqlcmd (on Windows it ships with SQL Server; on Linux, the mssql-tools18 package).
  • A backup directory with space equal to the largest database, and the ticket saying whom to warn before restoring anything.

1. Connect and see what is there

bash · srv-db-01
psql -h localhost -U postgres -c "\l+" | awk -F'|' 'NR>3 {print $1, $7}' | head -5 appdb        | 2412 MB postgres     | 7501 kBmysql -u root -p -e "SELECT table_schema AS db, ROUND(SUM(data_length+index_length)/1024/1024) AS mb FROM information_schema.tables GROUP BY table_schema ORDER BY mb DESC;"+--------------------+------+| db                 | mb   |+--------------------+------+| intranet           | 1870 || mysql              |    3 |+--------------------+------+sqlcmd -S srv-sql-01 -E -Q "SELECT name, (SELECT SUM(size)*8/1024 FROM sys.master_files mf WHERE mf.database_id=d.database_id) AS mb FROM sys.databases d ORDER BY mb DESC" -Wname    mbERP     38210tempdb  4096master  7

Before touching anything: which databases exist, how big they are and, in the ticket, which one belongs to the failing application. -E in sqlcmd uses your Windows account; in PostgreSQL and MySQL the admin account goes in ~/.pgpass and ~/.my.cnf with 600 permissions, never on a script's command line.

2. Backup and (tested) restore

bash · srv-db-01
# PostgreSQL: compressed logical backup and restore into a test databasepg_dump -h localhost -U postgres -Fc appdb -f /backup/appdb-$(date +%F).dumpcreatedb -h localhost -U postgres appdb_restore_test && pg_restore -h localhost -U postgres -d appdb_restore_test -j 4 /backup/appdb-$(date +%F).dumppsql -h localhost -U postgres -d appdb_restore_test -tAc "SELECT count(*) FROM orders;"184233# MySQLmysqldump --single-transaction --routines --triggers intranet | gzip > /backup/intranet-$(date +%F).sql.gzmysql -e "CREATE DATABASE intranet_restore_test" && zcat /backup/intranet-$(date +%F).sql.gz | mysql intranet_restore_test
sqlcmd · srv-sql-01
sqlcmd -S srv-sql-01 -E -Q "BACKUP DATABASE ERP TO DISK='E:\Backup\ERP_2026-10-22.bak' WITH COMPRESSION, CHECKSUM, STATS=10"10 percent processed. ... 100 percent processed.BACKUP DATABASE successfully processed 4891264 pages in 98.412 seconds (388.3 MB/sec).sqlcmd -S srv-sql-01 -E -Q "RESTORE VERIFYONLY FROM DISK='E:\Backup\ERP_2026-10-22.bak' WITH CHECKSUM"The backup set on file 1 is valid.sqlcmd -S srv-sql-01 -E -Q "RESTORE DATABASE ERP_restore_test FROM DISK='E:\Backup\ERP_2026-10-22.bak' WITH MOVE 'ERP' TO 'E:\Restore\ERP_test.mdf', MOVE 'ERP_log' TO 'E:\Restore\ERP_test.ldf', STATS=25"

A backup that has never been restored is a hope, not a backup. The test restore into another database and a count(*) of the main table is what you note in the monthly backup ticket (and what repo 33 on my GitHub, backup-restore-test-tracker, follows over time). If the server already has Veeam or a backup agent (Level 2), these commands are the verification, not the substitute. Drop the *_restore_test databases when done.

3. Disk space and growth

bash · srv-db-01
psql -h localhost -U postgres -d appdb -c "SELECT relname, pg_size_pretty(pg_total_relation_size(oid)) AS size, n_dead_tup FROM pg_stat_user_tables t JOIN pg_class c ON c.oid=t.relid ORDER BY pg_total_relation_size(oid) DESC LIMIT 3;"   relname    |  size   | n_dead_tup--------------+---------+------------ audit_log    | 1412 MB |    2203110 orders       |  611 MB |       8120psql -h localhost -U postgres -d appdb -c "VACUUM (VERBOSE, ANALYZE) audit_log;" 2>&1 | tail -1du -sh /var/lib/pgsql/16/data/pg_wal3.1G    /var/lib/pgsql/16/data/pg_wal
sqlcmd · srv-sql-01
sqlcmd -S srv-sql-01 -E -d ERP -Q "SELECT name, size*8/1024 AS mb, CAST(FILEPROPERTY(name,'SpaceUsed') AS int)*8/1024 AS used_mb FROM sys.database_files" -Wname     mb     used_mbERP      36120  29877ERP_log  2090   1912sqlcmd -S srv-sql-01 -E -Q "SELECT name, recovery_model_desc, log_reuse_wait_desc FROM sys.databases WHERE name='ERP'" -Wname  recovery_model_desc  log_reuse_wait_descERP   FULL                 LOG_BACKUP

The three usual culprits for a disk at 92%: in PostgreSQL, a table with millions of dead tuples without VACUUM (and pg_wal growing because a replication slot was orphaned); in MySQL, binlogs without expire_logs_days/binlog_expire_logs_seconds; in SQL Server, the transaction log in FULL recovery with no log backups: log_reuse_wait_desc = LOG_BACKUP says so, and the fix is scheduling BACKUP LOG, not SHRINKFILE every week.

4. User joiners and leavers

bash · srv-db-01
# PostgreSQL: read-only role for the analystpsql -h localhost -U postgres -d appdb -c "CREATE ROLE laura_prado LOGIN PASSWORD 'change-on-first-login'; GRANT CONNECT ON DATABASE appdb TO laura_prado; GRANT USAGE ON SCHEMA public TO laura_prado; GRANT SELECT ON ALL TABLES IN SCHEMA public TO laura_prado; ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO laura_prado;"# MySQLmysql -e "CREATE USER 'laura_prado'@'10.20.%' IDENTIFIED BY 'change-on-first-login' PASSWORD EXPIRE; GRANT SELECT ON intranet.* TO 'laura_prado'@'10.20.%';"# leaver: revoke and, if they own no objects, droppsql -h localhost -U postgres -d appdb -c "REASSIGN OWNED BY j_navarro TO postgres; DROP OWNED BY j_navarro; DROP ROLE j_navarro;"
sqlcmd · srv-sql-01
sqlcmd -S srv-sql-01 -E -Q "CREATE LOGIN [CONTOSO\sg-erp-readonly] FROM WINDOWS; USE ERP; CREATE USER [CONTOSO\sg-erp-readonly] FOR LOGIN [CONTOSO\sg-erp-readonly]; ALTER ROLE db_datareader ADD MEMBER [CONTOSO\sg-erp-readonly];"

The rule that holds for all three: group, not person, whenever the engine allows it. SQL Server with an AD group is the ideal: the leaver is handled in Active Directory (Level 1) and the database is never touched. In PostgreSQL and MySQL, a read role with GRANT to named users, and the user list in the quarterly access review.

5. "The application is slow": what is happening right now

bash · srv-db-01
psql -h localhost -U postgres -d appdb -c "SELECT pid, now()-query_start AS runtime, state, wait_event_type, left(query,60) AS query FROM pg_stat_activity WHERE state <> 'idle' AND pid <> pg_backend_pid() ORDER BY runtime DESC LIMIT 5;"  pid  |     runtime     | state  | wait_event_type |                   query-------+-----------------+--------+-----------------+---------------------------------------------- 18211 | 00:14:02.118    | active | Lock            | UPDATE orders SET status='X' WHERE customer_id=… 18240 | 00:13:58.004    | active | Lock            | SELECT * FROM orders WHERE customer_id=… 17990 | 00:14:40.771    | idle in transaction |  | BEGIN; UPDATE orders SET …psql -h localhost -U postgres -d appdb -c "SELECT pg_terminate_backend(17990);"mysql -e "SELECT id, user, time, state, LEFT(info,60) FROM information_schema.processlist WHERE command <> 'Sleep' ORDER BY time DESC LIMIT 5;"
sqlcmd · srv-sql-01
sqlcmd -S srv-sql-01 -E -Q "SELECT r.session_id, r.blocking_session_id, r.wait_type, r.wait_time/1000 AS wait_s, LEFT(t.text,60) AS sql_text FROM sys.dm_exec_requests r CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t WHERE r.session_id > 50 ORDER BY r.wait_time DESC" -Wsession_id  blocking_session_id  wait_type  wait_s  sql_text71          58                   LCK_M_U    812     UPDATE Invoices SET Status = …58          0                    NULL       0       (idle session with an open transaction)

The pattern is identical in all three: an idle session with an open transaction (someone opened a tool, ran BEGIN and went to lunch) blocks everyone else. pg_stat_activity, processlist and dm_exec_requests give you the pid of whoever is blocking; killing that session (pg_terminate_backend, KILL id, KILL session_id) brings the application back to life, and the ticket to the developer says which query and from which client. If there are no locks and it is a heavy query, EXPLAIN ANALYZE in PostgreSQL/MySQL or the execution plan in SSMS is the next step, and that one does belong to the DBA or the developer.

6. What you review each month

  • Test restore of each engine, with the count(*) noted.
  • Space: fastest-growing tables, pg_wal/binlogs/transaction log, and the six-month forecast from the trend.
  • Users: named accounts no longer in Active Directory; application account passwords in the manager with a date.
  • Engine patches: PostgreSQL and MySQL with the monthly Ansible patching; SQL Server with its quarterly CU in the change window, with a backup first.

Next steps

  • Monitoring: postgres_exporter, mysqld_exporter and the SQL Server one in the Prometheus from the Grafana guide; the "blocked sessions > 0 for 5 minutes" panel warns you before the user does.
  • High availability: streaming replica (PostgreSQL), replication (MySQL) and Always On (SQL Server) when the business won't accept an hour of downtime; before that, the tested restore already covers what matters.
  • Integrated authentication in PostgreSQL with Kerberos (Linux + AD guide): the same AD leaver closes the database too.

Is something missing, or different in your environment? Get in touch and I'll fix it.

Tech Career Lab · newsletter

¿Te ha servido? Cada dos semanas envío una idea práctica para perfiles IT: LinkedIn, proyectos, networking y procesos de selección.Found this useful? Every two weeks I send one practical idea for IT professionals: LinkedIn, projects, networking and hiring processes.

Apúntate y llévate las 33 ideasSign up and get the 33 ideas →