Also, you can see that there are WAL archive files, with one of it marked to be a special file for restores:
P.S.: The recovery.conf file will be renamed to recovery.done by the PostgreSQL process once the restore is finished.
Copy the base backup back to the clusters data directory:
Next, we gonna kill the database:
The one which ends on .backup contains information about when the backup was started and when it ended, so that a restore does not need to read and apply all WAL archive files in the archive, but only those written during and after it.
Once pg_basebackup completes you have a complete backup of the PostgreSQL cluster data:
One escape from this is to enable point-in-time-recovery.
When you start using (and developing with) it, you most likely use
pg_dump or pg_dumpall for backups.
Then start the server and once the recovery process is done query some data:
Now, lets create some random data to see that we have successfully enabled PITR and are able to use it.
After that you should see a new WAL in the archive directory:
Create a file /usr/local/pgsql/recovery.conf with this content:
It has a really good performance, has a large eco-system, has a maltitude of extensions for every special case and - what I love the most is rock-solid and offers an almost complete SQL standard implementation.
So the first step to enable PITR is to enable WAL-archiving. That basically means the finished WAL files are copied to a archive destination.
Enable the following settings in postgresql.conf:
If you take a look at the clusters log file you will notice, that it automatically recovered the WAL archives and the most recent data is present.
Once started they create a single SQL file containing both DDL and DML statements to restore or copy your database or a complete cluster (PostgreSQL terminology:the sum of all databases under control of a single PostgreSQL service, e.g. all databases on one machine).
With pg_dump you can only call it more often (from once a day to every hour) but sooner or later your database size will prevent the backup to be taken before the next run is started.
At this point the database cluster is gone and you will see error messages,
Add the following line to pg_hba.conf:
when you try to start it.
So, time to recover!
To get an idea please assume to have an archive directory structure as this,to which the postgres user has full access:
This ensures entegrity in the event of an outage or filesystem error.When the root cause is cleared and you restart the PostgreSQL server it reads the WAL and applies all changes or discards unfinished transactions.
After a default installation PostgreSQL is run in a non-archive mode.
That means that all changes are written to the WAL, Write Ahead Log, before they finally make it to the database files.
After doing so, PostgreSQL will copy all finished WAL files to the archive directory.
I really love PostgreSQL.
This was a very simple setup; trust me, productive environments usually have more complexity (archiving WAL files to remote servers, restore from tape, make full backups by taking filesystem snapshots etc. etc.).
Now you can take a full database backup any time by issuing commands like these:
Sooner or later you go into production and you want to minimize downtime - and thus dataloss - to the absolute minimum possible.
To make this example complete: add a cronjob entry which creates a full-backup every day, twice a day or even every hour and you should be able to recover in no time.
P.S.: You should not use pg_switch_wal without a reason. Consult the PostgreSQL manual for information about WAL configuration and switch timings!