P.S.: You should not use pg_switch_wal without a reason. Consult the PostgreSQL manual for information about WAL configuration and switch timings!
Add the following line to pg_hba.conf:
To get an idea please assume to have an archive directory structure as this,to which the postgres user has full access:
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:
when you try to start it.
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.
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.
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.
When you start using (and developing with) it, you most likely use
pg_dump or pg_dumpall for backups.
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, time to recover!
Now, lets create some random data to see that we have successfully enabled PITR and are able to use it.
One escape from this is to enable point-in-time-recovery.
After doing so, PostgreSQL will copy all finished WAL files to the archive directory.
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.
Now you can take a full database backup any time by issuing commands like these:
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.
Then start the server and once the recovery process is done query some data:
Also, you can see that there are WAL archive files, with one of it marked to be a special file for restores:
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.).
I really love PostgreSQL.
Once pg_basebackup completes you have a complete backup of the PostgreSQL cluster data:
Sooner or later you go into production and you want to minimize downtime - and thus dataloss - to the absolute minimum possible.
At this point the database cluster is gone and you will see error messages,
Enable the following settings in postgresql.conf:
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.
Next, we gonna kill the database:
After that you should see a new WAL in the archive directory:
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).
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.
Create a file /usr/local/pgsql/recovery.conf with this content: