Backing up SQL data without Transfer

14 June 2017

When working with data we typically make backups in case we make a mistake. In this age of high speed connections you still want to avoid transferring unnecessary data around, be it because it’s very large or you’re working over a cellphone connection. You can backup the data within the database.

If you only have a single schema available, create a new one for the purpose of backups.

% psql
p> CREATE SCHEMA new_schema;
p> SET search_path TO new_schema;

Setting the search_path makes the repl work from the prespective of the new schema.

To copy the table run

p> CREATE TABLE backup_table AS SELECT * FROM original_schema.original_table;

Now you can perform your risky operations on the original data with confidence.

If something bad does happen you can restore info by doing

p> TRUNCATE original_schema.original_table;
p> INSERT INTO original_schema.original_table SELECT * FROM backup_table;

For complete backups read Chapter 25: Backup and Restore. For a data backup solution try PGbackrest.

comments powered by Disqus