Databases commands

Back up a PostgreSQL database

Creates a logical backup of a PostgreSQL database.

pg_dump -U <username> -h <host> -d <database_name> > backup.sql

Add -Fc for a compressed custom-format dump that pg_restore can restore selectively.

Import a MySQL dump

Imports an SQL file into a MySQL database.

Caution. Dumps made by mysqldump include DROP TABLE statements by default, so existing tables are replaced.

mysql -u <username> -p <database_name> < backup.sql

Delete all Redis keys

Connects to redis-cli and deletes all keys in all databases.

Caution. Deletes every key in every database on the server. There is no undo.

redis-cli flushall

Restore a PostgreSQL custom-format dump

Restores a dump made with pg_dump -Fc, in a single transaction.

pg_restore -U <username> -h <host> -d <database_name> -1 backup.dump

pg_restore can't read a plain .sql dump; restore that with psql -d <database_name> -f backup.sql.

Back up a MySQL database

Dumps a MySQL database to an SQL file; --single-transaction gives a consistent InnoDB dump without locking tables.

mysqldump -u <username> -p --single-transaction <database_name> > backup.sql

Redis server info

Shows detailed information about the Redis server.

redis-cli info

Search Redis keys by pattern

Lists keys matching a glob-style pattern without blocking the server.

redis-cli --scan --pattern 'user:*'

Avoid KEYS in production: it blocks Redis while it walks every key.

Export MongoDB collection

Exports a MongoDB collection to a JSON file.

mongoexport --db=mydb --collection=users --out=users.json

Import MongoDB collection

Imports a JSON file into a MongoDB collection.

mongoimport --db=mydb --collection=users --file=users.json

Show active PostgreSQL connections

Shows all active connections and their states in PostgreSQL.

psql -c "SELECT pid, usename, state, query FROM pg_stat_activity WHERE state != 'idle';"

Check database size (PG)

Shows the size of each database in PostgreSQL.

psql -c "SELECT pg_database.datname, pg_size_pretty(pg_database_size(pg_database.datname)) FROM pg_database;"

Show the MySQL process list

Shows all currently running queries in MySQL.

mysql -e 'SHOW FULL PROCESSLIST;'

Find blocked PostgreSQL queries

Lists sessions waiting on a lock and the PIDs blocking them.

psql -c "SELECT pid, pg_blocking_pids(pid) AS blocked_by, state, wait_event_type, left(query, 60) AS query FROM pg_stat_activity WHERE cardinality(pg_blocking_pids(pid)) > 0;"

End a PostgreSQL session

Terminates a backend by PID, for example the one blocking others.

Caution. Ends the session and rolls back its open transaction. Try pg_cancel_backend(<backend-pid>) first, which cancels only the running query.

psql -c "SELECT pg_terminate_backend(<backend-pid>);"

Find the largest Redis keys

Samples the keyspace with SCAN and reports the biggest key of each type.

redis-cli --bigkeys

Uses SCAN, so it doesn't block the server, but it adds load; run it off-peak.