Backup of unlogged table with sequence is broken for PostgreSQL 14

[thisiself]

Describe the bug

When I try to restore a backup to PostgreSQL 14, I receive an error.

To Reproduce

Steps to reproduce the behavior:

1. Create an empty database (server PostgreSQL 14)
2. Execute following script:

CREATE UNLOGGED TABLE IF NOT EXISTS public.table1
(
    "Id" integer NOT NULL GENERATED ALWAYS AS IDENTITY ( INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 2147483647 CACHE 1 ),
    CONSTRAINT table1_pkey PRIMARY KEY ("Id")
)

3. Backup database (right-click on database, choose "Backup...", fill filename, all the rest - as default)
4. Try to restore backup (server PostgreSQL 14)
5. See the error

Expected behavior

A backup is restored without errors.

Error message

pg_restore: error: could not execute query: ERROR: "table1_Id_seq" is not a table
Command was: ALTER TABLE public.table1 ALTER COLUMN "Id" ADD GENERATED ALWAYS AS IDENTITY (
SEQUENCE NAME public."table1_Id_seq"
START WITH 1
INCREMENT BY 1
NO MINVALUE
NO MAXVALUE
CACHE 1
);
ALTER SEQUENCE public."table1_Id_seq" SET LOGGED;

Desktop (please complete the following information):

• OS: Windows 10, 22H2
• Version: 6.16, 6.17, 6.18
• Mode: Desktop

Additional context

I believe the bug was introduced with PostgreSQL 15 support, with its new feature of unlogged sequences. I guess the first version with this bug is 6.15, but i haven't tested it. Working versions I've tested - 6.9, 6.10, 6.11.

[pravesh-sharma]

Hi @thisiself

I have tested it on latest version of pgAdmin and is not reproducible can you please share some more details (screenshots, logs).

[thisiself]

Hi @pravesh-sharma !

I think the main problem here is that pgAdmin always uses pg_dump/pg_restore utils from the latest version of PostgreSQL Server, and these utils are incompatible for versions 14 and 15 for now. Try to make a backup with pgAdmin 6.18 (uses utils from version 15) and then restore the backup to PostgreSQL 14.* (in my case - 14.5).

If I open my backup with a text editor, i see line "ALTER SEQUENCE public."table1_Id_seq" SET LOGGED;" that is unsupported in PostgreSQL versions prior to 15. The backup file is here.

If i'm correct, i see 2 approaches to fix this:

1. Use different utils for different PostgreSQL versions ('select version()', then choose which utils to use)
2. Maybe this is an unwanted behavior and PostgreSQL developers can fix this somehow. I'm new to pgsql world, but i think this kind of problem must have appeared/resolved in past

More details/screenshots/logs i'll post a bit later, need to combine them.

[thisiself]

More details.

Versions

• pgAdmin: 6.18 (screenshot)
• PostgreSQL: PostgreSQL 14.5 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-10), 64-bit (screenshot)
• pg_dump.exe: file version 15.0.1.22342, product version 15.1
• pg_restore.exe: file version 15.0.1.22342, product version 15.1

Backup process

• Tab 1 screenshot
• Tab 2 screenshot
• Tab 3 screenshot
• Command:

C:\Users\***USERNAME***\AppData\Local\Programs\pgAdmin 4\v6\runtime\pg_dump.exe --file "C:\\Users\\***USERFOLDER***\\ISSUE5~1.BAK" --host "SRV-PGSQL-DEV" --port "5432" --username "postgres" --no-password --verbose --format=c --blobs "issue5678"

• Log:

pg_dump: reading extensions
pg_dump: identifying extension members
pg_dump: reading schemas
pg_dump: reading user-defined tables
pg_dump: reading user-defined functions
pg_dump: reading user-defined types
pg_dump: reading procedural languages
pg_dump: reading user-defined aggregate functions
pg_dump: reading user-defined operators
pg_dump: reading user-defined access methods
pg_dump: reading user-defined operator classes
pg_dump: reading user-defined operator families
pg_dump: reading user-defined text search parsers
pg_dump: reading user-defined text search templates
pg_dump: reading user-defined text search dictionaries
pg_dump: reading user-defined text search configurations
pg_dump: reading user-defined foreign-data wrappers
pg_dump: reading user-defined foreign servers
pg_dump: reading default privileges
pg_dump: reading user-defined collations
pg_dump: reading user-defined conversions
pg_dump: reading type casts
pg_dump: reading transforms
pg_dump: reading table inheritance information
pg_dump: reading event triggers
pg_dump: finding extension tables
pg_dump: finding inheritance relationships
pg_dump: reading column info for interesting tables
pg_dump: flagging inherited columns in subtables
pg_dump: reading indexes
pg_dump: flagging indexes in partitioned tables
pg_dump: reading extended statistics
pg_dump: reading constraints
pg_dump: reading triggers
pg_dump: reading rewrite rules
pg_dump: reading policies
pg_dump: reading row-level security policies
pg_dump: reading publications
pg_dump: reading publication membership of tables
pg_dump: reading publication membership of schemas
pg_dump: reading subscriptions
pg_dump: reading large objects
pg_dump: reading dependency data
pg_dump: saving encoding = UTF8
pg_dump: saving standard_conforming_strings = on
pg_dump: saving search_path =
pg_dump: saving database definition
pg_dump: dumping contents of table "public.table1"

Restore process

• Tab 1 screenshot
• Tab 2 screenshot
• Tab 3 screenshot
• Command:

C:\Users\***USERNAME***\AppData\Local\Programs\pgAdmin 4\v6\runtime\pg_restore.exe --host "SRV-PGSQL-DEV" --port "5432" --username "postgres" --no-password --dbname "issue5678_restore" --verbose "C:\\Users\\***USERFOLDER***\\ISSUE5~1.BAK"

• Log:

pg_restore: connecting to database for restore
pg_restore: creating SCHEMA "public"
pg_restore: creating TABLE "public.table1"
pg_restore: creating SEQUENCE "public.table1_Id_seq"
pg_restore: while PROCESSING TOC:
pg_restore: from TOC entry 209; 1259 1745625 SEQUENCE table1_Id_seq postgres
pg_restore: error: could not execute query: ERROR: "table1_Id_seq" is not a table
Command was: ALTER TABLE public.table1 ALTER COLUMN "Id" ADD GENERATED ALWAYS AS IDENTITY (
SEQUENCE NAME public."table1_Id_seq"
START WITH 1
INCREMENT BY 1
NO MINVALUE
NO MAXVALUE
CACHE 1
);
ALTER SEQUENCE public."table1_Id_seq" SET LOGGED;
pg_restore: processing data for table "public.table1"
pg_restore: executing SEQUENCE SET table1_Id_seq
pg_restore: from TOC entry 3306; 0 0 SEQUENCE SET table1_Id_seq postgres
pg_restore: error: could not execute query: ERROR: relation "public.table1_Id_seq" does not exist
LINE 1: SELECT pg_catalog.setval('public."table1_Id_seq"', 1, false)...
^
Command was: SELECT pg_catalog.setval('public."table1_Id_seq"', 1, false);
pg_restore: creating CONSTRAINT "public.table1 table1_pkey"
pg_restore: creating ACL "SCHEMA public"
pg_restore: warning: errors ignored on restore: 2

[akshay-joshi]

@thisiself

pgAdmin ships the latest binaries and it can't ship the binaries of all the supported database versions. You can specify the Binary Path from preferences-> Paths -> Binary paths for a specific database server version and then run the backup and restore.

You can log this issue to the PostgreSQL tracker.

[thisiself]

@akshay-joshi

Maybe its better to add some kind of warning to backup window? Something like "Warning! You're using utils from version XX to backup a database of version YY", if utils not match.

I think its better to receive a warning and think a bit, rather than receive a broken backup and understand that when restore fails some weeks after.

Or add feature of installing needed utils on demand. "Warning! ... Click here to install utils from version YY", user clicks, pgAdmin downloads necessary utils from somewhere, saves them and fills binary path.