{
"cells": [
{
"cell_type": "code",
"execution_count": null,
"id": "07a2db18",
"metadata": {},
"outputs": [],
"source": [
"import agh_db_lectures\n",
"agh_db_lectures.prepare_notebook_for_sql()"
]
},
{
"cell_type": "code",
"execution_count": null,
"id": "4f95fe96",
"metadata": {},
"outputs": [],
"source": [
"%%javascript\n",
"// Enable syntax highlighting of shell scripts.\n",
"require(['notebook/js/codecell'], codecell => {\n",
" const modes = codecell.CodeCell.options_default.highlight_modes;\n",
" modes['magic_shell'].reg.push(/^%%script sh/);\n",
"})"
]
},
{
"cell_type": "markdown",
"id": "14d2d634",
"metadata": {},
"source": [
"# Backups"
]
},
{
"cell_type": "markdown",
"id": "14dec48b",
"metadata": {},
"source": [
"## Failure Scenarios"
]
},
{
"cell_type": "markdown",
"id": "14ff9537",
"metadata": {},
"source": [
"- ORM has generated invalid SQL\n",
"- application (a database client) crashed\n",
"- there was a power shortage\n",
"- system ran out of disk space\n",
"- **storage media has worn out and failed**\n",
"- **someone executed `DROP` on production while learning SQL**\n",
"- **system got ransomware'd**\n",
"- …"
]
},
{
"cell_type": "markdown",
"id": "23f73651",
"metadata": {},
"source": [
"_notes_\n",
"\n",
"ORM are meant to be good at generating syntactically correct SQL. However, you can still get a semantically incorrect SQL when, for example, you try to migrate a Django model to a composite primary key.\n",
"\n",
"Now, what difference do you see between the first few scenarios and the bold ones at the bottom of the list?\n",
"\n",
"The last scenarios are not handled automatically by the DBMS."
]
},
{
"cell_type": "markdown",
"id": "8a1cb0dc",
"metadata": {},
"source": [
"## Backups Types (Postgres)"
]
},
{
"cell_type": "code",
"execution_count": null,
"id": "97716722",
"metadata": {},
"outputs": [],
"source": [
"%sql postgresql://demo_user:demo_pwd@localhost:25432/agh_it_northwind"
]
},
{
"cell_type": "code",
"execution_count": null,
"id": "71ae49d1",
"metadata": {},
"outputs": [],
"source": [
"agh_db_lectures.download_restore_nw_postgres_dump()"
]
},
{
"cell_type": "markdown",
"id": "ecd883e3",
"metadata": {},
"source": [
"_notes_\n",
"\n",
"We shall start with out Northwind database. It has the following tables."
]
},
{
"cell_type": "code",
"execution_count": null,
"id": "ef5fe136",
"metadata": {},
"outputs": [],
"source": [
"%%sql\n",
"SELECT * FROM pg_tables WHERE schemaname = 'public'"
]
},
{
"cell_type": "markdown",
"id": "277be050",
"metadata": {},
"source": [
"### Simple Filesystem Level Copy"
]
},
{
"cell_type": "code",
"execution_count": null,
"id": "2347f175",
"metadata": {},
"outputs": [],
"source": [
"!find /var/lib/postgresql/ -maxdepth 2"
]
},
{
"cell_type": "markdown",
"id": "d8e17036",
"metadata": {},
"source": [
"What do you think about just `tar`ing the files of the DBMS?"
]
},
{
"cell_type": "code",
"execution_count": null,
"id": "cc480f32",
"metadata": {},
"outputs": [],
"source": [
"!cd /var/lib/postgresql && tar -czf ~/backup.tgz ./"
]
},
{
"cell_type": "markdown",
"id": "ad782491",
"metadata": {},
"source": [
"_notes_\n",
"\n",
"Run the below script and try re-running the tar command above immediately after. Tar should complain about file(s) being modified while it processes them."
]
},
{
"cell_type": "code",
"execution_count": null,
"id": "16bb13a4",
"metadata": {},
"outputs": [],
"source": [
"%%script sh\n",
"while true; do\n",
" psql --port 25432 --quiet --command=\"VACUUM FULL;\" postgres\n",
"done >/dev/null 2>&1 &\n",
"(sleep 15 && kill $!) >/dev/null 2>&1 &"
]
},
{
"cell_type": "markdown",
"id": "2f21d210",
"metadata": {},
"source": [
"_notes_\n",
"\n",
"What could we do then?\n",
"\n",
"We could shut down the DBMS and perform backup while it is offline.\n",
"\n",
"Alternatively, we could leverage filesystem's feature of atomic snapshots."
]
},
{
"cell_type": "markdown",
"id": "343029db",
"metadata": {},
"source": [
"| filesystem | atomic snapshot support |\n",
"|:-|:-:|\n",
"| ext4 | no |\n",
"| zfs | yes |\n",
"| btrfs | yes |\n",
"| any filesystem over LVM | yes |"
]
},
{
"cell_type": "markdown",
"id": "19474337",
"metadata": {},
"source": [
"_notes_\n",
"\n",
"You can make a point-in-time copy of a **running** DBMS files using your filesystem's snapshotting functionality. Assuming that — as Postgres docs say — \"you are willing to trust that it is implemented correctly\"."
]
},
{
"cell_type": "markdown",
"id": "bbcd4b93",
"metadata": {},
"source": [
"Let's close our connection to Postgres."
]
},
{
"cell_type": "markdown",
"id": "537fd560",
"metadata": {},
"source": [
"_notes_\n",
"\n",
"We shall make a file level backup while the DBMS is not running."
]
},
{
"cell_type": "code",
"execution_count": null,
"id": "d21723a1",
"metadata": {},
"outputs": [],
"source": [
"conns = %sql -l\n",
"\n",
"def close_ipython_sql_connections():\n",
" for url, conn in list(conns.items()):\n",
" conn.internal_connection.invalidate()\n",
" conn.internal_connection.engine.dispose()\n",
" del conns[url]"
]
},
{
"cell_type": "code",
"execution_count": null,
"id": "95cfb398",
"metadata": {},
"outputs": [],
"source": [
"close_ipython_sql_connections()"
]
},
{
"cell_type": "code",
"execution_count": null,
"id": "778db546",
"metadata": {
"scrolled": true
},
"outputs": [],
"source": [
"%%script sh\n",
"# Stop Postgres.\n",
"kill $(cat /var/run/ferretdb.pid)\n",
"PSQL_PID=$(cat /var/run/postgresql.pid)\n",
"kill $PSQL_PID\n",
"while grep -qE '^State:[[:space:]]+[^Z[:space:]]' /proc/$PSQL_PID/status; do\n",
" sleep 1\n",
"done\n",
"\n",
"# Make backup while offline.\n",
"(cd /var/lib/postgresql && tar -czf ~/backup.tgz ./)\n",
"\n",
"# Start Postgres.\n",
"POSTGRES=$(realpath $(command -v postgres))\n",
"$POSTGRES -D /var/lib/postgresql/data -p 25432 -h 127.0.0.1 \\\n",
" >/var/log/postgresql.log 2>&1 &\n",
"printf %s $! >/var/run/postgresql.pid"
]
},
{
"cell_type": "markdown",
"id": "1491a1a1",
"metadata": {},
"source": [
"_notes_\n",
"\n",
"https://imgs.xkcd.com/comics/tar_2x.png"
]
},
{
"cell_type": "code",
"execution_count": null,
"id": "ba9189f1",
"metadata": {},
"outputs": [],
"source": [
"!tar -tf ~/backup.tgz"
]
},
{
"cell_type": "markdown",
"id": "350b9571",
"metadata": {},
"source": [
"_notes_\n",
"\n",
"As we can see above, our backup file contains all the files that used to be in the Postgres cluster directory."
]
},
{
"cell_type": "markdown",
"id": "5e38917d",
"metadata": {},
"source": [
"Let's simulate some disaster."
]
},
{
"cell_type": "code",
"execution_count": null,
"id": "9386a65f",
"metadata": {},
"outputs": [],
"source": [
"%sql postgresql://demo_user:demo_pwd@localhost:25432/agh_it_northwind"
]
},
{
"cell_type": "code",
"execution_count": null,
"id": "649c1db7",
"metadata": {},
"outputs": [],
"source": [
"%%sql\n",
"DROP TABLE employees CASCADE"
]
},
{
"cell_type": "markdown",
"id": "f08edc3c",
"metadata": {},
"source": [
"We need to restore from backup now :("
]
},
{
"cell_type": "code",
"execution_count": null,
"id": "b99526e6",
"metadata": {},
"outputs": [],
"source": [
"close_ipython_sql_connections()"
]
},
{
"cell_type": "markdown",
"id": "65d57307",
"metadata": {},
"source": [
"_notes_\n",
"\n",
"Of course, the DBMS must once again not be running for us to perform the operation. And for it to shut down gracefully, we have first closed our connections."
]
},
{
"cell_type": "code",
"execution_count": null,
"id": "5edbe8f8",
"metadata": {},
"outputs": [],
"source": [
"%%script sh\n",
"# Stop Postgres.\n",
"kill $(cat /var/run/ferretdb.pid)\n",
"PSQL_PID=$(cat /var/run/postgresql.pid)\n",
"kill $PSQL_PID\n",
"while grep -qE '^State:[[:space:]]+[^Z[:space:]]' /proc/$PSQL_PID/status; do\n",
" sleep 1\n",
"done\n",
"\n",
"# Restore backup while offline.\n",
"(cd /var/lib/postgresql/ && rm -rf ./* && tar -xzf ~/backup.tgz ./)\n",
"\n",
"# Start Postgres.\n",
"POSTGRES=$(realpath $(command -v postgres))\n",
"$POSTGRES -D /var/lib/postgresql/data -p 25432 -h 127.0.0.1 \\\n",
" >/var/log/postgresql.log 2>&1 &\n",
"printf %s $! >/var/run/postgresql.pid"
]
},
{
"cell_type": "markdown",
"id": "0d731e52",
"metadata": {},
"source": [
"Backup restored, let's reconnect."
]
},
{
"cell_type": "code",
"execution_count": null,
"id": "f2d13a9f",
"metadata": {},
"outputs": [],
"source": [
"%sql postgresql://demo_user:demo_pwd@localhost:25432/agh_it_northwind"
]
},
{
"cell_type": "code",
"execution_count": null,
"id": "8e10c5d6",
"metadata": {},
"outputs": [],
"source": [
"%%sql\n",
"SELECT first_name, last_name FROM employees"
]
},
{
"cell_type": "markdown",
"id": "7a060edc",
"metadata": {},
"source": [
"### SQL Dump"
]
},
{
"cell_type": "markdown",
"id": "6f042569",
"metadata": {},
"source": [
"_notes_\n",
"\n",
"`pg_dump` tool can be used to produce a dump of a catalog (database) in SQL syntax.\n",
"\n",
"Note that it also has the capability of dumping a single table or even just table's data, without DDL."
]
},
{
"cell_type": "code",
"execution_count": null,
"id": "1fb34549",
"metadata": {},
"outputs": [],
"source": [
"!pg_dump --port 25432 agh_it_northwind | gzip > ~/nw-backup.sql.gz"
]
},
{
"cell_type": "markdown",
"id": "fd0809cf",
"metadata": {},
"source": [
"_notes_\n",
"\n",
"Switch to an example with compression.\n",
"\n",
"```shell\n",
"pg_dump --port 25432 agh_it_northwind | gzip > ~/nw-backup.sql.gz\n",
"```\n",
"\n",
"Note that `pg_dump` is merely an application that connects to the server. It works in a transaction without stopping other operations."
]
},
{
"cell_type": "code",
"execution_count": null,
"id": "8fe7be6c",
"metadata": {
"scrolled": true
},
"outputs": [],
"source": [
"!cat ~/nw-backup.sql"
]
},
{
"cell_type": "markdown",
"id": "0c433471",
"metadata": {},
"source": [
"_notes_\n",
"\n",
"```shell\n",
"zcat ~/nw-backup.sql.gz\n",
"```\n",
"\n",
"Note the \n",
"\n",
"- use of less flexible yet faster `COPY` command instead of `INSERT`\n",
" - (it can be used to read to table's data from a CSV file, btw.),\n",
"- re-creation of constraints **after** the database is populated"
]
},
{
"cell_type": "code",
"execution_count": null,
"id": "eab6fd92",
"metadata": {},
"outputs": [],
"source": [
"!pg_dumpall --port 25432 | gzip > ~/backup.sql.gz"
]
},
{
"cell_type": "markdown",
"id": "a5102d7c",
"metadata": {},
"source": [
"_notes_\n",
"\n",
"`pg_dumpall` is `pg_dump`'s friend that can backup an entire cluster, with all databases it contains."
]
},
{
"cell_type": "code",
"execution_count": null,
"id": "648e87db",
"metadata": {},
"outputs": [],
"source": [
"!dropdb --port 25432 --if-exists agh_it_nw_restored\n",
"!createdb --port 25432 agh_it_nw_restored\n",
"!zcat ~/nw-backup.sql.gz | psql --port 25432 agh_it_nw_restored"
]
},
{
"cell_type": "markdown",
"id": "bc87ef27",
"metadata": {},
"source": [
"_notes_\n",
"\n",
"The additional benefit of a single-database dump, though, is that we can restore it to a differently-named database, as above."
]
},
{
"cell_type": "code",
"execution_count": null,
"id": "30ca145d",
"metadata": {},
"outputs": [],
"source": [
"%%script sh\n",
"CMD='SELECT product_name FROM products LIMIT 5'\n",
"psql --port 25432 --command=\"$CMD\" agh_it_nw_restored"
]
},
{
"cell_type": "markdown",
"id": "36a0f8f0",
"metadata": {},
"source": [
"_notes_\n",
"\n",
"We can see that the data is there, in the newly created database that is now a clone of the old one."
]
},
{
"cell_type": "code",
"execution_count": null,
"id": "aee8e69c",
"metadata": {},
"outputs": [],
"source": [
"!cat /var/lib/postgresql/data/postgresql.conf"
]
},
{
"cell_type": "markdown",
"id": "b28e753e",
"metadata": {},
"source": [
"_notes_\n",
"\n",
"Postgres automatically creates a config file in the data dir."
]
},
{
"cell_type": "code",
"execution_count": null,
"id": "d59d2e04",
"metadata": {},
"outputs": [],
"source": [
"!grep 'maintenance_work_mem =' /var/lib/postgresql/data/postgresql.conf"
]
},
{
"cell_type": "markdown",
"id": "b8f711c1",
"metadata": {},
"source": [
"_notes_\n",
"\n",
"Increasing the amout of memory available to maintenance operations can speed up large restores."
]
},
{
"cell_type": "code",
"execution_count": null,
"id": "66d98593",
"metadata": {},
"outputs": [],
"source": [
"!psql --port 25432 --command 'ANALYZE;' agh_it_nw_restored"
]
},
{
"cell_type": "markdown",
"id": "6f9695e1",
"metadata": {},
"source": [
"_notes_\n",
"\n",
"Running `ANALYZE` after restoration \"is strongly recommended\". It allows Postgres to collect up-to-date statistics about the data."
]
},
{
"cell_type": "markdown",
"id": "53f816eb",
"metadata": {},
"source": [
"### Custom Format Dump"
]
},
{
"cell_type": "code",
"execution_count": null,
"id": "2d425af5",
"metadata": {},
"outputs": [],
"source": [
"!pg_dump --format c --port 25432 agh_it_northwind > ~/nw-backup.bin"
]
},
{
"cell_type": "code",
"execution_count": null,
"id": "f985923a",
"metadata": {},
"outputs": [],
"source": [
"!cat ~/nw-backup.bin"
]
},
{
"cell_type": "markdown",
"id": "b5eae82c",
"metadata": {},
"source": [
"_notes_\n",
"\n",
"As can be seen above, it is a binary format with some pieces of textual data present."
]
},
{
"cell_type": "code",
"execution_count": null,
"id": "8a605b28",
"metadata": {},
"outputs": [],
"source": [
"!gzip < ~/nw-backup.bin > ~/nw-backup.bin.gz"
]
},
{
"cell_type": "markdown",
"id": "903be120",
"metadata": {},
"source": [
"_notes_\n",
"\n",
"There's no reason not to compress the backup in this format as well."
]
},
{
"cell_type": "code",
"execution_count": null,
"id": "6a798bbd",
"metadata": {},
"outputs": [],
"source": [
"!dropdb --port 25432 --if-exists agh_it_nw_restored\n",
"!createdb --port 25432 agh_it_nw_restored\n",
"!zcat ~/nw-backup.bin.gz | pg_restore --port 25432 -d agh_it_nw_restored"
]
},
{
"cell_type": "markdown",
"id": "8ad79e83",
"metadata": {},
"source": [
"_notes_\n",
"\n",
"To restore from this binary backup, we use `pg_restore`, while previously we could just pipe the SQL code to any dump client of the database."
]
},
{
"cell_type": "code",
"execution_count": null,
"id": "bf327c9a",
"metadata": {},
"outputs": [],
"source": [
"%%script sh\n",
"CMD='SELECT product_name FROM products LIMIT 5'\n",
"psql --port 25432 --command=\"$CMD\" agh_it_nw_restored"
]
},
{
"cell_type": "markdown",
"id": "2c4b558d",
"metadata": {},
"source": [
"How do you think, which backup type is the largest one?"
]
},
{
"cell_type": "code",
"execution_count": null,
"id": "71a1e9e7",
"metadata": {},
"outputs": [],
"source": [
"!ls -lhd ~/*backup* | sed 's/\\(\\S\\+\\s\\+\\)\\{4\\}//' | sort -rh"
]
},
{
"cell_type": "markdown",
"id": "05ed2750",
"metadata": {},
"source": [
"_notes_\n",
"\n",
"Note that filesystem level backup can only be applied to entire cluster, not to individual databases. OTOH, `pg_dump` (or `pg_dumpall`) can be used to backup the entire cluster, a single database, or just a single table.\n",
"\n",
"The entire cluster backup made with `pg_dumpall` can be significantly smaller than a filesystem level backup.\n",
"\n",
"`pg_dump` backups in SQL and other formats are comparable in size if compression is used."
]
},
{
"cell_type": "markdown",
"id": "0bf47337",
"metadata": {},
"source": [
"### Online Backup"
]
},
{
"cell_type": "code",
"execution_count": null,
"id": "fdea98ee",
"metadata": {},
"outputs": [],
"source": [
"!ls -lh /var/lib/postgresql/data/pg_wal"
]
},
{
"cell_type": "markdown",
"id": "e1e7d187",
"metadata": {},
"source": [
"The **Write Ahead Log** is written to files under `pg_wal/`. Data written to it enables failure recovery and transactional processing of database operations.\n",
"\n",
"The WAL can also be leveraged for automated, incremental backups."
]
},
{
"cell_type": "code",
"execution_count": null,
"id": "311b459e",
"metadata": {},
"outputs": [],
"source": [
"%%script sh\n",
"CONF=/var/lib/postgresql/data/postgresql.conf\n",
"grep -E 'archive_(command|mode) =' \"$CONF\""
]
},
{
"cell_type": "markdown",
"id": "46afc1f3",
"metadata": {},
"source": [
"_notes_\n",
"\n",
"Incremental backup (the \"on-line backup\") involves copying of WAL files to some kind of archive (which itself can be implemented however we see fit). We just need to turn this feature on and provide a command that Postgres shall use to copy subsequent WAL files."
]
},
{
"cell_type": "code",
"execution_count": null,
"id": "7fa53234",
"metadata": {},
"outputs": [],
"source": [
"%%script sh\n",
"DST=\"$HOME/online-backup\"\n",
"CMD=\"test \\! -f $DST/%f \\\\&\\\\& cp %p $DST/%f\"\n",
"CONF=/var/lib/postgresql/data/postgresql.conf\n",
"sed -i s,'^#\\?\\(archive_command\\) = .*',\"\\\\1 = '$CMD'\", $CONF\n",
"sed -i s,'^#\\?\\(archive_mode\\) = .*',\"\\\\1 = on\", $CONF\n",
"\n",
"rm -rf $DST\n",
"mkdir -p $DST"
]
},
{
"cell_type": "code",
"execution_count": null,
"id": "31b6a5b8",
"metadata": {},
"outputs": [],
"source": [
"%%script sh\n",
"CONF=/var/lib/postgresql/data/postgresql.conf\n",
"grep -E 'archive_(command|mode) =' \"$CONF\""
]
},
{
"cell_type": "markdown",
"id": "ff4a02dd",
"metadata": {},
"source": [
"_notes_\n",
"\n",
"The simplest approach is to just `cp` the file. `%f` and `%p` in the command act as placeholders to be substituted with file's name and path, respectively."
]
},
{
"cell_type": "markdown",
"id": "84bb93b1",
"metadata": {},
"source": [
"Let's initialize the backup."
]
},
{
"cell_type": "code",
"execution_count": null,
"id": "183af4db",
"metadata": {},
"outputs": [],
"source": [
"close_ipython_sql_connections()"
]
},
{
"cell_type": "markdown",
"id": "96e3b981",
"metadata": {},
"source": [
"_notes_\n",
"\n",
"We need to restart Postgres to pick up our last config changes."
]
},
{
"cell_type": "code",
"execution_count": null,
"id": "d72a26f1",
"metadata": {},
"outputs": [],
"source": [
"%%script sh\n",
"# Stop Postgres.\n",
"kill $(cat /var/run/ferretdb.pid)\n",
"PSQL_PID=$(cat /var/run/postgresql.pid)\n",
"kill $PSQL_PID\n",
"while grep -qE '^State:[[:space:]]+[^Z[:space:]]' /proc/$PSQL_PID/status; do\n",
" sleep 1\n",
"done\n",
"\n",
"# Start Postgres.\n",
"POSTGRES=$(realpath $(command -v postgres))\n",
"$POSTGRES -D /var/lib/postgresql/data -p 25432 -h 127.0.0.1 \\\n",
" >/var/log/postgresql.log 2>&1 &\n",
"printf %s $! >/var/run/postgresql.pid"
]
},
{
"cell_type": "markdown",
"id": "d6f3e54f",
"metadata": {},
"source": [
"_notes_\n",
"\n",
"The initial backup (the \"base\" that can later be incremented) could be done with `tar` (exceptionally, we could even do this wihle the db is runnning — the WAL files would later allow recovery even with inappropriately copied other files).\n",
"\n",
"However, instead of `tar` we can use the convenient `pg_basebackup` that also does some other necessary things automatically under the hood."
]
},
{
"cell_type": "code",
"execution_count": null,
"id": "a271066e",
"metadata": {},
"outputs": [],
"source": [
"%%script sh\n",
"rm -rf ~/online-backup/base/\n",
"mkdir -p ~/online-backup/base/\n",
"pg_basebackup --port 25432 \\\n",
" --pgdata ~/online-backup/base/ \\\n",
" --format t --gzip"
]
},
{
"cell_type": "code",
"execution_count": null,
"id": "cbb5dc43",
"metadata": {},
"outputs": [],
"source": [
"!ls ~/online-backup/base/"
]
},
{
"cell_type": "code",
"execution_count": null,
"id": "72bc33ee",
"metadata": {},
"outputs": [],
"source": [
"!cat ~/online-backup/base/backup_manifest"
]
},
{
"cell_type": "code",
"execution_count": null,
"id": "b31c62bf",
"metadata": {},
"outputs": [],
"source": [
"!tar -tf ~/online-backup/base/base.tar.gz"
]
},
{
"cell_type": "code",
"execution_count": null,
"id": "c41b7272",
"metadata": {},
"outputs": [],
"source": [
"!tar -tf ~/online-backup/base/pg_wal.tar.gz"
]
},
{
"cell_type": "markdown",
"id": "56e78994",
"metadata": {},
"source": [
"_notes_\n",
"\n",
"The `base.tar.gz` contains almost all the files of the database cluster."
]
},
{
"cell_type": "code",
"execution_count": null,
"id": "264f0c73",
"metadata": {},
"outputs": [],
"source": [
"!ls -lh ~/online-backup"
]
},
{
"cell_type": "markdown",
"id": "6cf9f802",
"metadata": {},
"source": [
"_notes_\n",
"\n",
"This is where future WAL files shall be copied into."
]
},
{
"cell_type": "code",
"execution_count": null,
"id": "8019a0d7",
"metadata": {},
"outputs": [],
"source": [
"!psql --port 25432 --command 'SELECT pg_switch_wal();' postgres"
]
},
{
"cell_type": "markdown",
"id": "f4e99335",
"metadata": {},
"source": [
"_notes_\n",
"\n",
"Postgres would normally create a new (by default 16MB) write-ahead-log file when it fully fills the previous one. We would then see the file copied to our archive dir.\n",
"\n",
"We can force early finishing of a WAL file with function `pg_switch_wal()`. After executing the above cell a few times, list the `online-backup/` directory again to see that the files have indeed been copied by our `archive_command`. We could normally use this function after executing an important transaction that we want to be archived."
]
},
{
"cell_type": "markdown",
"id": "9b442d09",
"metadata": {},
"source": [
"Assume our Postgres installation got nuked and we need to restore from backup."
]
},
{
"cell_type": "code",
"execution_count": null,
"id": "799e39bc",
"metadata": {},
"outputs": [],
"source": [
"%%script sh\n",
"# Stop Postgres.\n",
"kill $(cat /var/run/ferretdb.pid)\n",
"PSQL_PID=$(cat /var/run/postgresql.pid)\n",
"kill $PSQL_PID\n",
"while grep -qE '^State:[[:space:]]+[^Z[:space:]]' /proc/$PSQL_PID/status; do\n",
" sleep 1\n",
"done"
]
},
{
"cell_type": "code",
"execution_count": null,
"id": "f1a7dab5",
"metadata": {},
"outputs": [],
"source": [
"!rm -rf /var/lib/postgresql/data/*"
]
},
{
"cell_type": "markdown",
"id": "9f199246",
"metadata": {},
"source": [
"Restoring…"
]
},
{
"cell_type": "code",
"execution_count": null,
"id": "bd0fb59c",
"metadata": {},
"outputs": [],
"source": [
"%%script sh\n",
"BASE=~/online-backup/base/base.tar.gz\n",
"WAL=~/online-backup/base/pg_wal.tar.gz\n",
"\n",
"(cd /var/lib/postgresql/data/ && tar -xzf \"$BASE\")\n",
"(cd /var/lib/postgresql/data/pg_wal/ && tar -xvzf \"$WAL\")\n",
"# If we were able to recover the WAL files that were in the\n",
"# data dir of the broken database cluster, we could copy\n",
"# them back to `pg_wal/'."
]
},
{
"cell_type": "code",
"execution_count": null,
"id": "037589e4",
"metadata": {},
"outputs": [],
"source": [
"# Once we start restoring, Postgres should copy the other WAL\n",
"# files from archive to `pg_wal/'.\n",
"!ls /var/lib/postgresql/data/{,pg_wal/}"
]
},
{
"cell_type": "code",
"execution_count": null,
"id": "5f03ebfe",
"metadata": {},
"outputs": [],
"source": [
"%%script sh\n",
"CONF=/var/lib/postgresql/data/postgresql.conf\n",
"grep -E 'restore_command =' \"$CONF\""
]
},
{
"cell_type": "markdown",
"id": "37a2fb57",
"metadata": {},
"source": [
"_notes_\n",
"\n",
"Postgres expects an extra `restore_command` option that tells it how to fetch subsequent archived WAL files while restoring from backup. "
]
},
{
"cell_type": "code",
"execution_count": null,
"id": "12854377",
"metadata": {},
"outputs": [],
"source": [
"%%script sh\n",
"SRC=\"$HOME/online-backup\"\n",
"CMD=\"cp $SRC/%f %p\"\n",
"CONF=/var/lib/postgresql/data/postgresql.conf\n",
"sed -i s,'^#\\?\\(restore_command\\) = .*',\"\\\\1 = '$CMD'\", $CONF"
]
},
{
"cell_type": "code",
"execution_count": null,
"id": "ca39a1e4",
"metadata": {},
"outputs": [],
"source": [
"%%script sh\n",
"CONF=/var/lib/postgresql/data/postgresql.conf\n",
"grep -E 'restore_command =' \"$CONF\""
]
},
{
"cell_type": "markdown",
"id": "70c7be50",
"metadata": {},
"source": [
"_notes_\n",
"\n",
"`restore_command` is analogous to `archive_command`."
]
},
{
"cell_type": "code",
"execution_count": null,
"id": "235b5a1d",
"metadata": {},
"outputs": [],
"source": [
"!touch /var/lib/postgresql/data/recovery.signal"
]
},
{
"cell_type": "markdown",
"id": "6aa6efd1",
"metadata": {},
"source": [
"_notes_\n",
"\n",
"To start the recovery we need to additionally create a `recovery.signal` file inside the data directory. Its does not need any contents — it merely acts as a flag."
]
},
{
"cell_type": "code",
"execution_count": null,
"id": "dc56bafb",
"metadata": {},
"outputs": [],
"source": [
"%%script sh\n",
"# Start Postgres.\n",
"POSTGRES=$(realpath $(command -v postgres))\n",
"$POSTGRES -D /var/lib/postgresql/data -p 25432 -h 127.0.0.1 \\\n",
" >/var/log/postgresql.log 2>&1 &\n",
"printf %s $! >/var/run/postgresql.pid"
]
},
{
"cell_type": "markdown",
"id": "e081249c",
"metadata": {},
"source": [
"When started, the DBMS automatically starts the restoration procedure with the use of WAL files."
]
},
{
"cell_type": "code",
"execution_count": null,
"id": "5b5cbafe",
"metadata": {},
"outputs": [],
"source": [
"!ls /var/lib/postgresql/data/pg_wal/"
]
},
{
"cell_type": "markdown",
"id": "8d9991be",
"metadata": {},
"source": [
"_notes_\n",
"\n",
"We should now see the WAL files copied back."
]
},
{
"cell_type": "code",
"execution_count": null,
"id": "fffb8ecc",
"metadata": {},
"outputs": [],
"source": [
"%%script sh\n",
"CMD='SELECT product_name FROM products LIMIT 5'\n",
"psql --port 25432 --command=\"$CMD\" agh_it_nw_restored"
]
},
{
"cell_type": "markdown",
"id": "0a9102dd",
"metadata": {},
"source": [
"_notes_\n",
"\n",
"We see that the restoration succeeded."
]
},
{
"cell_type": "code",
"execution_count": null,
"id": "2d68e00b",
"metadata": {},
"outputs": [],
"source": [
"# Postgres removes the file once reovery is finished.\n",
"!ls /var/lib/postgresql/data/recovery.signal"
]
}
],
"metadata": {
"kernelspec": {
"display_name": "Python 3 (ipykernel)",
"language": "/gnu/store/pv50hmdxs15c32laa1vn03wkfl023wwk-python-3.11.14/bin/python3",
"name": "python3"
},
"language_info": {
"codemirror_mode": {
"name": "ipython",
"version": 3
},
"file_extension": ".py",
"mimetype": "text/x-python",
"name": "python",
"nbconvert_exporter": "python",
"pygments_lexer": "ipython3",
"version": "3.11.14"
}
},
"nbformat": 4,
"nbformat_minor": 5
}
