Solving failing manual backup and restore operations

Manual SQL backup and restore fails

Problem

When creating or restoring backup manually (i.e. via SQL Administration tools such as pgAdmin, or directly via SQL commands), some errors may occur.

Cause

These issues are caused by using pg_dump and pg_restore versions that are not compatible with PostgreSQL 16 (on which Serverless SQL Databases rely).

Solution

To solve these issues, upgrade your pg_dump and/or pg_restore modules:

• You can upgrade them by installing PostgreSQL 16 which includes these tools.
• If you are using a third-party tool that includes these libraries, upgrade your tool. For instance, pgAdmin supports PostgreSQL 16 ecosystem from version 7.8.

Automated backup fails due to long-running queries

Problem

When performing long-running queries (lasting several hours or days), backup operations might not be performed successfully. Backup status will then appear as error or unknown_status.

Cause

These issues are caused by queries locking database rows (usually long running transactions), preventing logical backup operations from reading database rows.

Solution

To solve these issues, stop these queries:

• List PostgreSQL processes and identify the ones that have been running transactions since several hours (‘xact_start’ colmun) with the following command:

  SELECT pid, state, usename, query, xact_start, query_start FROM pg_stat_activity ORDER BY xact_start;
  

• Stop the corresponding queries with:

  SELECT pg_cancel_backend({pid});
  

  Where {pid} is the process id from the long-running query causing the issue (pid column of the previous step).

Alternatively, you can also stop long-running queries using a graphical PostgreSQL client such as pgAdmin or DBeaver.