SQL Snippets

From NixNet
Revision as of 02:05, 9 July 2021 by Amolith (talk | contribs) (add database dumps/restores)

Some random commands I always forget when I need them

It isn’t 100% necessary to put your SQL commands in caps. It is, however, considered best practice as it helps differentiate between what are commands and what are arguments.


Working with users

  • Create a DB: CREATE DATABASE database_name;
  • Create a user: CREATE USER 'user_name'@'localhost' IDENTIFIED BY 'password';
  • Grant user permissions: GRANT ALL ON database_name.* TO 'user_name'@'localhost' IDENTIFIED BY 'password';
  • List all users: SELECT User FROM mysql.user;

Obviously you should change password to an actually secure password, user_name to something identifiable, and the same for database_name. Even if it’s one user per database, it’s better to use different names for each one.

Working with databases

  • List all databases: SHOW DATABASES;
  • Select a database to interact with: USE database;
  • List tables: SHOW TABLES;
  • Show contents of a table: SELECT * FROM table;
  • Delete a row: DELETE FROM table WHERE column=value;
  • Dumping databases for backups: mysqldump dbname > dbname_$(date -u -Iseconds).sql (run as shell command)
    • Dumps with compression: mysqldump dbname | gzip -c - > /root/dumps/dbname_$(date -u -Iseconds).sql.gz
  • Restore database from backup: mysql dbname < backup.sql
    • With compression: gunzip backup.sql.gz then run command above


  • List databases: \l
  • Create user: CREATE ROLE app_user WITH LOGIN ENCRYPTED PASSWORD 'password';
  • Create database: CREATE DATABASE app WITH OWNER app_user;
  • Modify database owner: ALTER DATABASE app OWNER TO new_app_user;
  • Connect/use database: \c <database>


Looks like a lot of work but it's not bad. This is just every little step.

  • Install new version
  • Check the config files for differences and selectively modify new version to match old
    • Configs are at /etc/postgresql/VER/main/postgresql.conf and …/main/pg_hba.conf
  • Stop the postgresql systemd service
    • systemctl stop postgresql
  • sudo su - postgres
  • Check the clusters with the following (doesn't actually run any migrations)
/usr/lib/postgresql/NEW_VER/bin/pg_upgrade \
  --old-datadir=/var/lib/postgresql/OLD_VER/main \
  --new-datadir=/var/lib/postgresql/NEW_VER/main \
  --old-bindir=/usr/lib/postgresql/OLD_VER/bin \
  --new-bindir=/usr/lib/postgresql/NEW_VER/bin \
  --old-options '-c config_file=/etc/postgresql/OLD_VER/main/postgresql.conf' \
  --new-options '-c config_file=/etc/postgresql/NEW_VER/main/postgresql.conf' \
  • Assuming it all looks good, run the migration with the above snippet without --check
  • Log out of postgres and back into root
  • Swap postgres's ports in the config files
    • In the new config, replace port = 5433 with port = 5432
    • In the old config, replace port = 5432 with port = 5433
  • Start the postgresql systemd service
    • systemctl start postgresql
  • Log into postgres user again and make sure it's running the right version
    • psql -c "SELECT version();"
  • Run the generated script
    • ./analyze_new_cluster.sh
  • Exit to root user
  • List installed packages and grep for postgres to remove old versions
    • apt list --installed | grep postgres
    • apt remove postgres-OLD_VER postgres-OLD_VER…
  • Make sure things are running smoothly then rm -rf /etc/postgresql/OLD_VER
  • Log back into postgres user again and drop the old cluster data with ./delete_old_cluster.sh