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
– 0xPunt, Commented Oct 6, 2022 at 17:32

0xPunt · Accepted Answer · 2022-10-06 17:30:09Z

0

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.

answered Oct 6, 2022 at 17:30

0xPunt

3211 gold badge4 silver badges23 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

pg_restore not restoring sequences value

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related