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.
If you need help solving your business problems with software read how to hire me.