PostgreSQL – Fix pg_dump and pg_restore Input File Does Not Appear to Be a Valid Archive

I also just ran into this issue. The key here is actually the archive_cleanup_command in the "recovery.conf" on the standby. The standby will run the archive_cleanup_command command when it is done processing a WAL segment from the primary, so at that point you know you can backup that WAL segment and all prior segments. In my "recovery.conf" I have:

archive_cleanup_command = '/var/lib/postgresql/wal_backup_mirror.sh "%r"'

The contents of that script are (simplified version):

CURRENT_WAL_FILE="$1"
for WAL_FILE in $(find /pg_logs/main -maxdepth 1 -type f | sort | awk "\$0 <= \"/pg_logs/main/${CURRENT_WAL_FILE}\""); do
  WAL_NAME=$(basename "$WAL_FILE")
  gzip -c "$WAL_FILE" > "/backups/wal/${WAL_NAME}.gz"
  #now upload the just created .gz to S3 or some other offsite storage

  rm -f "${WAL_FILE}"
done

Note here that I delete the WAL segment after backing it up to keep my log directory clean on the standby, but the one caveat there is to be careful of a cascaded replication setup, since a standby that is further down the chain might still need those files.

One final note, remember that backing up the WAL segments isn't sufficient, it has to be done in combination with some sort of regular full backup (pg_basebackup). We do full backups daily, and then just backup the WAL segments as needed throughout the day.

You should create the uauvuro0s8b9v4 user prior to restore the database or the pg_restore will raise such errors (as this user's owns a lot of relations, in your case).

In psql try:

CREATE USER uauvuro0s8b9v4 WITH PASSWORD '12334444';