Consider a case were you recognized that some entries in your production database are missing or corrupted. If you are lucky enough you have a recent backup in place, but now the question arises, how can we only restore the missing entries off the affected tables. By using PostgreSQL’s Foreign Data Wrapper you can ease the process of recovering the data by some extend. The following steps show a possible way to achieve this.
First create a Database for the restore, this can either be done on the backup server or as an additional database on production.
CREATE DATABASE recovery;
Now restore the backups of the affected table (mytable) to the new database.
pg_restore -d recovery -t mytable backup.pgdump
The next steps are all done on the database server with the lost data. First we need to install the Foreign Data Wrapper for PostgreSQL.
CREATE EXTENSION postgres_fdw;
Now create a foreign server, replace 127.0.0.1 with the IP address or the name of the host were your restored the database previously.
CREATE SERVER recovery_server FOREIGN DATA WRAPPER postgres_fdw OPTIONS (host '127.0.0.1', dbname 'recovery' );
PostgreSQL needs to know how to login to the remote server. This is done with a user mapping like below. Replace myuser and mypassword with a user that has at least read privileges on the table(s) you just restored.
CREATE USER MAPPING FOR postgres SERVER recovery_server OPTIONS (user 'myuser', password 'mypassword');
Now create a new schema
CREATE SCHEMA recovery;
Create foreign tables in the new created schema. This step imports the table definitions of all restored tables to the new schema.
IMPORT FOREIGN SCHEMA public FROM SERVER recovery_server INTO recovery;
After the previous step you are able to access the restored table as well as the broken table with SQL. So for example you can restore entries like this.
INSERT INTO public.mytable SELECT * FROM recovery.mytable WHERE id NOT IN (SELECT id FROM public.mytable);
By doing it this way you have a very effective way to partial restore data from a table to recover from failure. You should remember to cleanup the FDW Stuff afterwards. Some additional notes about the procedure.
- IMPORT FOREIGN SCHEMA requires at least PostgreSQL 9.5
- PostgreSQL 10 improves the Performance of Foreign Tables by enabling push down of aggregates
Read more about PostgreSQL Foreign Data Wrapper in the PostgreSQL Documentation:
