Re: Using pg hotbackup directly without wal archives

Greetings,

* Ayush Goyal (ayush@helpshift.com) wrote:
> I need to ask if there is a way to use the pg hot backup taken via
> pg_start_backup() and pg_stop_backup() calls directly without using the wal
> archives.

No, WAL is required for correct backups.  There are multiple ways to
ensure that the WAL is archived.

> If I start postgres with just base backup without setting up recovery.conf
> for fetching archived wal logs, I get this im the log files:
>
> 2016-10-26 06:48:25 UTC LOG:  database system was shut down in recovery at
> 2016-10-25 15:50:16 UTC
> 2016-10-26 06:48:25 UTC LOG:  invalid primary checkpoint record
> 2016-10-26 06:48:25 UTC LOG:  invalid secondary checkpoint record
> 2016-10-26 06:48:25 UTC PANIC:  could not locate a valid checkpoint record
> 2016-10-26 06:48:25 UTC LOG:  startup process (PID 980) was terminated by
> signal 6: Aborted
> 2016-10-26 06:48:25 UTC LOG:  aborting startup due to startup process
> failure
>
> Also, I noticed that if I run pg_resetxlog, I am able to start postgres,
> but I am not  sure if the database is consistent after running
> pg_resetxlog. Is database state consistent after running pg_resetxlog on
> this hot backup?

No, the database state will *not* be consistent after pg_resetxlog.  You
must archive the WAL which is written during the backup and must provide
a recovery.conf when performing a restore.

I strongly recommend that you look at using the existing PG backup tools
which will correctly perform the backup and WAL archiving, such as
pg_basebackup, pgBackRest, or barman.

Thanks!

Stephen