Postgres backup verification error - The server failed to start

[MScallion]

First time executing the backup was this morning and I cannot get the verification to work. We do not have a public schema in our database, hopefully it is not required as it was not for the backup. I have attempted the verification several times. Any help in steps that I can take to investigate the cause of this issue is appreciated.

Namespace = 30245e-dev.

sh-4.2$ ./backup.sh -s -v all

Verifying backup ...

Settings:
- Database: postgres=patroni-master-dev/mals
- Backup file: /backups/daily/2021-07-27/patroni-master-dev-mals_2021-07-27_11-12-00.sql.gz

Already clean ...

waiting for server to start.....................................................................................................................
The server failed to start within 0h:2m:0s.


The server started in 0h:2m:0s.


waiting for server to shut down.... done
server stopped
Cleaning up ...

[!!ERROR!!] - Backup verification failed: /backups/daily/2021-07-27/patroni-master-dev-mals_2021-07-27_11-12-00.sql.gz

Elapsed time: 0h:2m:1s - Status Code: 1

[WadeBarnes]

In the run above, the verification is failing because the local server instance (used for validation) did not start within the timeout period set for DATABASE_SERVER_TIMEOUT; in this case 2 minutes. You can increase the time by setting DATABASE_SERVER_TIMEOUT to a higher value, for example;

There are other factors that can cause the server to start slowly such as CPU and Memory resource allocation. The backup-container performs best when allowed to use "best-effort" resource allocation, which is the default specified in the example template; https://github.com/BCDevOps/backup-container/blob/master/openshift/templates/backup/backup-deploy.yaml#L428-L447

[WadeBarnes]

If your database does not use a "public" schema, you can specify the name of your database's schema via the TABLE_SCHEMA variable. This will tell the backup-container which schema to backup and verify.

https://github.com/BCDevOps/backup-container/blob/master/openshift/templates/backup/backup-deploy.yaml#L293-L297

[MScallion]

Thanks Wade. Those four parameter values are set to the defaults and the table_schema parameter is set to the appropriate schema. I added the timeout parameter with 300 seconds and the verification now times out in 5 minutes.

I have updated the Dockerfile to pull Postgres v13 as our app database is v13. Hopefully that isn't the cause of the issue.

FROM centos/postgresql-13-centos7

[WadeBarnes]

That could be the issue. We haven't tested the backup-container with that version. It could be that some startup parameters have changed. What's the version of the database you're backing up?

[MScallion]

Full version:

 PostgreSQL 13.3 (Debian 13.3-1.pgdg100+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 8.3.0-6) 8.3.0, 64-bit

I will redo the backup build using v12 for to see if it is compatible.

[WadeBarnes]

It's typically preferable to have the backup-container use the same or newer version as the database you are backing up. Would you be up for troubleshooting the startup issues and contributing the upgrade back to the project? I can provide you with some guidance on how to go about troubleshooting.

[MScallion]

Certainly. Keep in mind that this is my first and only experience with OpenShift and I am still learning how everything works/interacts.

[WadeBarnes]

Open a terminal on a running version of your backup-container. The you'll run the following command after replacing the <DatabaseName>, <DatabaseUser> and <DatabasePassword> with some real values:

POSTGRESQL_DATABASE=<DatabaseName> \
POSTGRESQL_USER=<DatabaseUser> \
POSTGRESQL_PASSWORD=<DatabasePassword> \
run-postgresql

For the purpose of troubleshooting it does not really matter what the values are for <DatabaseName>, <DatabaseUser> and <DatabasePassword>. Running the command will start start the server and you'll be able to see the log output and hopefully find out where it's failing.

You're basically mimicking what's done by this function, https://github.com/BCDevOps/backup-container/blob/master/docker/backup.postgres.plugin#L104-L123, without suppressing the log output.

[basilv]

I'm interested in this as well, using Postgis extension of Postgres 13, and just starting to look at using backup container.

[MScallion]

Below is the output;

sh-4.2$ POSTGRESQL_DATABASE=mals \
> POSTGRESQL_USER=postgres \
> POSTGRESQL_PASSWORD=##### \
> run-postgresql
The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.

The database cluster will be initialized with locale "en_US.utf8".
The default database encoding has accordingly been set to "UTF8".
The default text search configuration will be set to "english".

Data page checksums are disabled.

fixing permissions on existing directory /var/lib/pgsql/data/userdata ... ok
creating subdirectories ... ok
selecting dynamic shared memory implementation ... posix
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting default time zone ... PST8PDT
creating configuration files ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... ok
syncing data to disk ... ok

initdb: warning: enabling "trust" authentication for local connections
You can change this by editing pg_hba.conf or using the option -A, or
--auth-local and --auth-host, the next time you run initdb.

Success. You can now start the database server using:

    pg_ctl -D /var/lib/pgsql/data/userdata -l logfile start

waiting for server to start....2021-07-28 13:25:13.746 PDT [38077] LOG:  redirecting log output to logging collector process
2021-07-28 13:25:13.746 PDT [38077] HINT:  Future log output will appear in directory "log".
 done
server started
/var/run/postgresql:5432 - accepting connections
createuser: error: creation of new role failed: ERROR:  role "postgres" already exists

[WadeBarnes]

I'm interested in this as well, using Postgis extension of Postgres 13, and just starting to look at using backup container.

Once the upgrade is complete, the Postgis extension would need to be installed on the backup-container for your use case. It should be a simple update to the dockerfile.

[WadeBarnes]

@MScallion, Other then the createuser: error: creation of new role failed: ERROR: role "postgres" already exists, it looks like it's running. So you're issue may be due to the way the container is configured.

What's the output from ./backup.sh -c?

[MScallion]

Settings:
- Run mode: cron

- Backup strategy: rolling
- Current backup type: daily
- Backups to retain:
  - Daily: 1
  - Weekly: 1
  - Monthly: 1
- Current backup folder: /backups/daily/2021-07-28/
- Time Zone: PDT -0700

- Schedule:
  - 0 1 * * * default ./backup.sh -s
  - 0 4 * * * default ./backup.sh -s -v all

- Container Type: postgres
- Databases (filtered by container type):
  - postgres=patroni-master-dev/mals

- FTP server: not configured
- Webhook Endpoint: https://parcsystems.webhook.office.com/webhookb2/14b3e8f2-8035-4b3b-bf09-82f0b981616e@0d413f05-310c-4aad-8859-8943f3ded1f0/IncomingWebhook/8ac18ec5da754f5dbd7701dbb173c63e/9d0970f2-adfc-4c59-b638-053c71980087
- Environment Friendly Name: MALS
- Environment Name (Id): dev

[MScallion]

Should the Environment Name be the namespace?

[WadeBarnes]

Should the Environment Name be the namespace?

Yes, but that shouldn't effect the verification.