pg_restore not restoring sequences value

Question

I have 2 databases : dbprod and dbdev, with 2 users uprod and udev.

What I'd like to do is to copy the production database to my development database.

I succeeded to restore my database, but for some reason the value of my sequences are not set.

Here's what I'm doing :

// 1. Dumping
pg_dump --file '/home/punt/dump_db/prod.tar.gz' --format=t --schema 'public' 'dbprod'

// 2. Renaming the public schema to public_old
psql --dbname dbdev -c 'ALTER SCHEMA public RENAME TO public_old;'

// 3. Restoring
pg_restore --dbname='dbdev' --format=t --verbose /home/punt/dump_db/prod.tar.gz

// 4. And then I have to handle GRANTs...

Everything is working fine, except that the value of the sequences are not set.

I checked the content of my prod.tar.gz and I have the create and set, ex:

///...
CREATE SEQUENCE public.blockchain_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;

ALTER TABLE public.blockchain_id_seq OWNER TO uprod;

CREATE SEQUENCE public.cryptocurrency_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE public.cryptocurrency_id_seq OWNER TO uprod;

//...

SELECT pg_catalog.setval('public.blockchain_id_seq', 34, true);
SELECT pg_catalog.setval('public.cryptocurrency_id_seq', 148, true);

And the --verbose does not show errors :

pg_restore: creating SEQUENCE "public.cryptocurrency_id_seq"
pg_restore: creating SEQUENCE "public.blockchain_id_seq"

// ...


pg_restore: executing SEQUENCE SET blockchain_id_seq
pg_restore: executing SEQUENCE SET cryptocurrency_id_seq

I'm thinking maybe some grants/privileges issue ?

Any idea ? Thanks.

psql --version
psql (PostgreSQL) 11.17 (Debian 11.17-0+deb10u1)

@LaurenzAlbe I find the solution. I'm no expert Postgres, was "--clean --no-owner --no-privileges" the missing part ? Thanks. — 0xPunt, Oct 06 '22 at 17:32

score 0 · Answer 1 · answered Oct 06 '22 at 17:30

So... I don't know what was wrong...

But here's the solution, based on this : Postgresql - backup database and restore on different owner?

sudo -i -u postgres pg_dump --file /home/punt/dump_db/prod.tar.gz --format=t --clean --no-owner --no-privileges --schema public dbprod

sudo -i -u postgres psql --dbname dbdev -c 'ALTER SCHEMA public RENAME TO public_old;'

sudo -i -u postgres pg_restore --host localhost --username udev --password --dbname dbdev --no-owner --no-privileges --format t --verbose /home/punt/dump_db/prod.tar.gz

And then we can DROP public_old if we want. The solution with dropdb/createdb is also working, but I prefer to save the old schema and drop it when I need to.

pg_restore not restoring sequences value

1 Answers1