3

I'm transferring all of the databases on my old postgres server to a new server. To do this I'm using pg_dumpall and then pg_restore. However, it appears that no sequences have been dumped. This is not the first time I've run into this problem. I've seen the same problem when backing up individual databases using pg_dump.

Searching has turned up a few similar problems, but they appear to be related to dumping/restoring a single table. In my case, I'm dumping the entire database so I can't figure out why the sequences are not getting restored.

How can I investigate this problem further and/or resolve the issue so that I'm not stuck rebuilding the sequences on every single table on my server?

The command I use to create the backup:

pg_dump --host localhost --port 5432 --username "postgres" --format custom --blobs --file /home/spencerrecneps/backups/census.backup census

The command I use to restore the backup on a clean server:

pg_restore -Cv -h localhost -p 5432 -U postgres -d postgres ./census.backup > err.txt 2>&1

The result of pg_restore -l is:

;
; Archive created at Mon Sep 28 10:12:32 2015
;     dbname: census
;     TOC Entries: 63
;     Compression: -1
;     Dump Version: 1.12-0
;     Format: CUSTOM
;     Integer: 4 bytes
;     Offset: 8 bytes
;     Dumped from database version: 9.4.1
;     Dumped by pg_dump version: 9.4.1
;
;
; Selected TOC Entries:
;
3363; 1262 19819 DATABASE - census gis
7; 2615 27405 SCHEMA - iowa gis
8; 2615 27406 SCHEMA - nebraska gis
5; 2615 2200 SCHEMA - public postgres
3364; 0 0 COMMENT - SCHEMA public postgres
3365; 0 0 ACL - public postgres
9; 2615 27407 SCHEMA - united_states gis
10; 2615 27408 SCHEMA - wisconsin gis
204; 3079 11898 EXTENSION - plpgsql 
3366; 0 0 COMMENT - EXTENSION plpgsql 
205; 3079 19820 EXTENSION - postgis 
3367; 0 0 COMMENT - EXTENSION postgis 
192; 1259 27409 TABLE iowa block_pop_hu_2010 gis
193; 1259 27415 SEQUENCE iowa block_pop_hu_2010_pk_uid_seq gis
3368; 0 0 SEQUENCE OWNED BY iowa block_pop_hu_2010_pk_uid_seq gis
194; 1259 27417 TABLE nebraska block_pop_hu_2010 gis
195; 1259 27423 SEQUENCE nebraska block_pop_hu_2010_pk_uid_seq gis
3369; 0 0 SEQUENCE OWNED BY nebraska block_pop_hu_2010_pk_uid_seq gis
196; 1259 27425 TABLE united_states state_2010 gis
197; 1259 27431 SEQUENCE united_states state_2010_id_seq gis
3370; 0 0 SEQUENCE OWNED BY united_states state_2010_id_seq gis
198; 1259 27433 TABLE wisconsin block_pop_hu_2010 gis
199; 1259 27439 SEQUENCE wisconsin block_pop_hu_pk_uid_seq gis
3371; 0 0 SEQUENCE OWNED BY wisconsin block_pop_hu_pk_uid_seq gis
200; 1259 27441 TABLE wisconsin county_2010 gis
201; 1259 27447 SEQUENCE wisconsin county_2010_id_seq gis
3372; 0 0 SEQUENCE OWNED BY wisconsin county_2010_id_seq gis
202; 1259 27449 TABLE wisconsin place_2012 gis
203; 1259 27455 SEQUENCE wisconsin place_2012_pk_uid_seq gis
3373; 0 0 SEQUENCE OWNED BY wisconsin place_2012_pk_uid_seq gis
3206; 2604 27457 DEFAULT iowa pk_uid gis
3207; 2604 27458 DEFAULT nebraska pk_uid gis
3208; 2604 27459 DEFAULT united_states id gis
3209; 2604 27460 DEFAULT wisconsin pk_uid gis
3210; 2604 27461 DEFAULT wisconsin id gis
3211; 2604 27462 DEFAULT wisconsin pk_uid gis
3347; 0 27409 TABLE DATA iowa block_pop_hu_2010 gis
3374; 0 0 SEQUENCE SET iowa block_pop_hu_2010_pk_uid_seq gis
3349; 0 27417 TABLE DATA nebraska block_pop_hu_2010 gis
3375; 0 0 SEQUENCE SET nebraska block_pop_hu_2010_pk_uid_seq gis
3204; 0 20088 TABLE DATA public spatial_ref_sys postgres
3351; 0 27425 TABLE DATA united_states state_2010 gis
3376; 0 0 SEQUENCE SET united_states state_2010_id_seq gis
3353; 0 27433 TABLE DATA wisconsin block_pop_hu_2010 gis
3377; 0 0 SEQUENCE SET wisconsin block_pop_hu_pk_uid_seq gis
3355; 0 27441 TABLE DATA wisconsin county_2010 gis
3378; 0 0 SEQUENCE SET wisconsin county_2010_id_seq gis
3357; 0 27449 TABLE DATA wisconsin place_2012 gis
3379; 0 0 SEQUENCE SET wisconsin place_2012_pk_uid_seq gis
3213; 2606 33510 CONSTRAINT iowa block_pop_hu_2010_pkey gis
3216; 2606 33512 CONSTRAINT nebraska block_pop_hu_2010_pkey gis
3220; 2606 33514 CONSTRAINT united_states state_2010_pkey gis
3222; 2606 33516 CONSTRAINT wisconsin block_pop_hu_pkey gis
3225; 2606 33518 CONSTRAINT wisconsin county_2010_pkey gis
3228; 2606 33520 CONSTRAINT wisconsin place_2012_pkey gis
3214; 1259 33521 INDEX iowa sidx_block_pop_hu_2010_the_geom gis
3217; 1259 33522 INDEX nebraska sidx_block_pop_hu_2010_the_geom gis
3218; 1259 33523 INDEX united_states sidx_state_2010 gis
3223; 1259 33524 INDEX wisconsin sidx_block_pop_hu_the_geom gis
3226; 1259 33525 INDEX wisconsin sidx_county_2010 gis
3229; 1259 33526 INDEX wisconsin sidx_place_2012_the_geom gis

And here is the output captured from running the restore:

pg_restore: connecting to database for restore
pg_restore: creating DATABASE census
pg_restore: connecting to new database "census"
pg_restore: connecting to database "census" as user "postgres"
pg_restore: creating SCHEMA iowa
pg_restore: creating SCHEMA nebraska
pg_restore: creating SCHEMA public
pg_restore: creating COMMENT SCHEMA public
pg_restore: creating SCHEMA united_states
pg_restore: creating SCHEMA wisconsin
pg_restore: creating EXTENSION plpgsql
pg_restore: creating COMMENT EXTENSION plpgsql
pg_restore: creating EXTENSION postgis
pg_restore: creating COMMENT EXTENSION postgis
pg_restore: creating TABLE block_pop_hu_2010
pg_restore: creating SEQUENCE block_pop_hu_2010_pk_uid_seq
pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 193; 1259 27415 SEQUENCE block_pop_hu_2010_pk_uid_seq gis
pg_restore: [archiver (db)] could not execute query: ERROR:  syntax error at or near "USING"
LINE 7:     USING local;
            ^
    Command was: CREATE SEQUENCE block_pop_hu_2010_pk_uid_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1...
pg_restore: [archiver (db)] could not execute query: ERROR:  relation "iowa.block_pop_hu_2010_pk_uid_seq" does not exist
    Command was: ALTER TABLE iowa.block_pop_hu_2010_pk_uid_seq OWNER TO gis;


pg_restore: creating SEQUENCE OWNED BY block_pop_hu_2010_pk_uid_seq
pg_restore: [archiver (db)] Error from TOC entry 3368; 0 0 SEQUENCE OWNED BY block_pop_hu_2010_pk_uid_seq gis
pg_restore: [archiver (db)] could not execute query: ERROR:  relation "block_pop_hu_2010_pk_uid_seq" does not exist
    Command was: ALTER SEQUENCE block_pop_hu_2010_pk_uid_seq OWNED BY block_pop_hu_2010.pk_uid;



pg_restore: creating TABLE block_pop_hu_2010
pg_restore: creating SEQUENCE block_pop_hu_2010_pk_uid_seq
pg_restore: [archiver (db)] Error from TOC entry 195; 1259 27423 SEQUENCE block_pop_hu_2010_pk_uid_seq gis
pg_restore: [archiver (db)] could not execute query: ERROR:  syntax error at or near "USING"
LINE 7:     USING local;
            ^
    Command was: CREATE SEQUENCE block_pop_hu_2010_pk_uid_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1...
pg_restore: [archiver (db)] could not execute query: ERROR:  relation "nebraska.block_pop_hu_2010_pk_uid_seq" does not exist
    Command was: ALTER TABLE nebraska.block_pop_hu_2010_pk_uid_seq OWNER TO gis;


pg_restore: creating SEQUENCE OWNED BY block_pop_hu_2010_pk_uid_seq
pg_restore: [archiver (db)] Error from TOC entry 3369; 0 0 SEQUENCE OWNED BY block_pop_hu_2010_pk_uid_seq gis
pg_restore: [archiver (db)] could not execute query: ERROR:  relation "block_pop_hu_2010_pk_uid_seq" does not exist
    Command was: ALTER SEQUENCE block_pop_hu_2010_pk_uid_seq OWNED BY block_pop_hu_2010.pk_uid;



pg_restore: creating TABLE state_2010
pg_restore: creating SEQUENCE state_2010_id_seq
pg_restore: [archiver (db)] Error from TOC entry 197; 1259 27431 SEQUENCE state_2010_id_seq gis
pg_restore: [archiver (db)] could not execute query: ERROR:  syntax error at or near "USING"
LINE 7:     USING local;
            ^
    Command was: CREATE SEQUENCE state_2010_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1
    USING ...
pg_restore: [archiver (db)] could not execute query: ERROR:  relation "united_states.state_2010_id_seq" does not exist
    Command was: ALTER TABLE united_states.state_2010_id_seq OWNER TO gis;


pg_restore: creating SEQUENCE OWNED BY state_2010_id_seq
pg_restore: [archiver (db)] Error from TOC entry 3370; 0 0 SEQUENCE OWNED BY state_2010_id_seq gis
pg_restore: [archiver (db)] could not execute query: ERROR:  relation "state_2010_id_seq" does not exist
    Command was: ALTER SEQUENCE state_2010_id_seq OWNED BY state_2010.id;



pg_restore: creating TABLE block_pop_hu_2010
pg_restore: creating SEQUENCE block_pop_hu_pk_uid_seq
pg_restore: [archiver (db)] Error from TOC entry 199; 1259 27439 SEQUENCE block_pop_hu_pk_uid_seq gis
pg_restore: [archiver (db)] could not execute query: ERROR:  syntax error at or near "USING"
LINE 7:     USING local;
            ^
    Command was: CREATE SEQUENCE block_pop_hu_pk_uid_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1
    ...
pg_restore: [archiver (db)] could not execute query: ERROR:  relation "wisconsin.block_pop_hu_pk_uid_seq" does not exist
    Command was: ALTER TABLE wisconsin.block_pop_hu_pk_uid_seq OWNER TO gis;


pg_restore: creating SEQUENCE OWNED BY block_pop_hu_pk_uid_seq
pg_restore: [archiver (db)] Error from TOC entry 3371; 0 0 SEQUENCE OWNED BY block_pop_hu_pk_uid_seq gis
pg_restore: [archiver (db)] could not execute query: ERROR:  relation "block_pop_hu_pk_uid_seq" does not exist
    Command was: ALTER SEQUENCE block_pop_hu_pk_uid_seq OWNED BY block_pop_hu_2010.pk_uid;



pg_restore: creating TABLE county_2010
pg_restore: creating SEQUENCE county_2010_id_seq
pg_restore: [archiver (db)] Error from TOC entry 201; 1259 27447 SEQUENCE county_2010_id_seq gis
pg_restore: [archiver (db)] could not execute query: ERROR:  syntax error at or near "USING"
LINE 7:     USING local;
            ^
    Command was: CREATE SEQUENCE county_2010_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1
    USING...
pg_restore: [archiver (db)] could not execute query: ERROR:  relation "wisconsin.county_2010_id_seq" does not exist
    Command was: ALTER TABLE wisconsin.county_2010_id_seq OWNER TO gis;


pg_restore: creating SEQUENCE OWNED BY county_2010_id_seq
pg_restore: [archiver (db)] Error from TOC entry 3372; 0 0 SEQUENCE OWNED BY county_2010_id_seq gis
pg_restore: [archiver (db)] could not execute query: ERROR:  relation "county_2010_id_seq" does not exist
    Command was: ALTER SEQUENCE county_2010_id_seq OWNED BY county_2010.id;



pg_restore: creating TABLE place_2012
pg_restore: creating SEQUENCE place_2012_pk_uid_seq
pg_restore: [archiver (db)] Error from TOC entry 203; 1259 27455 SEQUENCE place_2012_pk_uid_seq gis
pg_restore: [archiver (db)] could not execute query: ERROR:  syntax error at or near "USING"
LINE 7:     USING local;
            ^
    Command was: CREATE SEQUENCE place_2012_pk_uid_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1
    US...
pg_restore: [archiver (db)] could not execute query: ERROR:  relation "wisconsin.place_2012_pk_uid_seq" does not exist
    Command was: ALTER TABLE wisconsin.place_2012_pk_uid_seq OWNER TO gis;


pg_restore: creating SEQUENCE OWNED BY place_2012_pk_uid_seq
pg_restore: [archiver (db)] Error from TOC entry 3373; 0 0 SEQUENCE OWNED BY place_2012_pk_uid_seq gis
pg_restore: [archiver (db)] could not execute query: ERROR:  relation "place_2012_pk_uid_seq" does not exist
    Command was: ALTER SEQUENCE place_2012_pk_uid_seq OWNED BY place_2012.pk_uid;



pg_restore: creating DEFAULT pk_uid
pg_restore: [archiver (db)] Error from TOC entry 3206; 2604 27457 DEFAULT pk_uid gis
pg_restore: [archiver (db)] could not execute query: ERROR:  relation "block_pop_hu_2010_pk_uid_seq" does not exist
    Command was: ALTER TABLE ONLY block_pop_hu_2010 ALTER COLUMN pk_uid SET DEFAULT nextval('block_pop_hu_2010_pk_uid_seq'::regclass);



pg_restore: creating DEFAULT pk_uid
pg_restore: [archiver (db)] Error from TOC entry 3207; 2604 27458 DEFAULT pk_uid gis
pg_restore: [archiver (db)] could not execute query: ERROR:  relation "block_pop_hu_2010_pk_uid_seq" does not exist
    Command was: ALTER TABLE ONLY block_pop_hu_2010 ALTER COLUMN pk_uid SET DEFAULT nextval('block_pop_hu_2010_pk_uid_seq'::regclass);



pg_restore: creating DEFAULT id
pg_restore: [archiver (db)] Error from TOC entry 3208; 2604 27459 DEFAULT id gis
pg_restore: [archiver (db)] could not execute query: ERROR:  relation "state_2010_id_seq" does not exist
    Command was: ALTER TABLE ONLY state_2010 ALTER COLUMN id SET DEFAULT nextval('state_2010_id_seq'::regclass);



pg_restore: creating DEFAULT pk_uid
pg_restore: [archiver (db)] Error from TOC entry 3209; 2604 27460 DEFAULT pk_uid gis
pg_restore: [archiver (db)] could not execute query: ERROR:  relation "block_pop_hu_pk_uid_seq" does not exist
    Command was: ALTER TABLE ONLY block_pop_hu_2010 ALTER COLUMN pk_uid SET DEFAULT nextval('block_pop_hu_pk_uid_seq'::regclass);



pg_restore: creating DEFAULT id
pg_restore: [archiver (db)] Error from TOC entry 3210; 2604 27461 DEFAULT id gis
pg_restore: [archiver (db)] could not execute query: ERROR:  relation "county_2010_id_seq" does not exist
    Command was: ALTER TABLE ONLY county_2010 ALTER COLUMN id SET DEFAULT nextval('county_2010_id_seq'::regclass);



pg_restore: creating DEFAULT pk_uid
pg_restore: [archiver (db)] Error from TOC entry 3211; 2604 27462 DEFAULT pk_uid gis
pg_restore: [archiver (db)] could not execute query: ERROR:  relation "place_2012_pk_uid_seq" does not exist
    Command was: ALTER TABLE ONLY place_2012 ALTER COLUMN pk_uid SET DEFAULT nextval('place_2012_pk_uid_seq'::regclass);



pg_restore: processing data for table "block_pop_hu_2010"
pg_restore: executing SEQUENCE SET block_pop_hu_2010_pk_uid_seq
pg_restore: [archiver (db)] Error from TOC entry 3374; 0 0 SEQUENCE SET block_pop_hu_2010_pk_uid_seq gis
pg_restore: [archiver (db)] could not execute query: ERROR:  relation "block_pop_hu_2010_pk_uid_seq" does not exist
LINE 1: SELECT pg_catalog.setval('block_pop_hu_2010_pk_uid_seq', 216...
                                 ^
    Command was: SELECT pg_catalog.setval('block_pop_hu_2010_pk_uid_seq', 216007, true);



pg_restore: processing data for table "block_pop_hu_2010"
pg_restore: executing SEQUENCE SET block_pop_hu_2010_pk_uid_seq
pg_restore: [archiver (db)] Error from TOC entry 3375; 0 0 SEQUENCE SET block_pop_hu_2010_pk_uid_seq gis
pg_restore: [archiver (db)] could not execute query: ERROR:  relation "block_pop_hu_2010_pk_uid_seq" does not exist
LINE 1: SELECT pg_catalog.setval('block_pop_hu_2010_pk_uid_seq', 193...
                                 ^
    Command was: SELECT pg_catalog.setval('block_pop_hu_2010_pk_uid_seq', 193352, true);



pg_restore: processing data for table "spatial_ref_sys"
pg_restore: processing data for table "state_2010"
pg_restore: executing SEQUENCE SET state_2010_id_seq
pg_restore: [archiver (db)] Error from TOC entry 3376; 0 0 SEQUENCE SET state_2010_id_seq gis
pg_restore: [archiver (db)] could not execute query: ERROR:  relation "state_2010_id_seq" does not exist
LINE 1: SELECT pg_catalog.setval('state_2010_id_seq', 52, true);
                                 ^
    Command was: SELECT pg_catalog.setval('state_2010_id_seq', 52, true);



pg_restore: processing data for table "block_pop_hu_2010"
pg_restore: executing SEQUENCE SET block_pop_hu_pk_uid_seq
pg_restore: [archiver (db)] Error from TOC entry 3377; 0 0 SEQUENCE SET block_pop_hu_pk_uid_seq gis
pg_restore: [archiver (db)] could not execute query: ERROR:  relation "block_pop_hu_pk_uid_seq" does not exist
LINE 1: SELECT pg_catalog.setval('block_pop_hu_pk_uid_seq', 253096, ...
                                 ^
    Command was: SELECT pg_catalog.setval('block_pop_hu_pk_uid_seq', 253096, true);



pg_restore: processing data for table "county_2010"
pg_restore: executing SEQUENCE SET county_2010_id_seq
pg_restore: [archiver (db)] Error from TOC entry 3378; 0 0 SEQUENCE SET county_2010_id_seq gis
pg_restore: [archiver (db)] could not execute query: ERROR:  relation "county_2010_id_seq" does not exist
LINE 1: SELECT pg_catalog.setval('county_2010_id_seq', 72, true);
                                 ^
    Command was: SELECT pg_catalog.setval('county_2010_id_seq', 72, true);



pg_restore: processing data for table "place_2012"
pg_restore: executing SEQUENCE SET place_2012_pk_uid_seq
pg_restore: [archiver (db)] Error from TOC entry 3379; 0 0 SEQUENCE SET place_2012_pk_uid_seq gis
pg_restore: [archiver (db)] could not execute query: ERROR:  relation "place_2012_pk_uid_seq" does not exist
LINE 1: SELECT pg_catalog.setval('place_2012_pk_uid_seq', 773, true)...
                                 ^
    Command was: SELECT pg_catalog.setval('place_2012_pk_uid_seq', 773, true);



pg_restore: creating CONSTRAINT block_pop_hu_2010_pkey
pg_restore: creating CONSTRAINT block_pop_hu_2010_pkey
pg_restore: creating CONSTRAINT state_2010_pkey
pg_restore: creating CONSTRAINT block_pop_hu_pkey
pg_restore: creating CONSTRAINT county_2010_pkey
pg_restore: creating CONSTRAINT place_2012_pkey
pg_restore: creating INDEX sidx_block_pop_hu_2010_the_geom
pg_restore: creating INDEX sidx_block_pop_hu_2010_the_geom
pg_restore: creating INDEX sidx_state_2010
pg_restore: creating INDEX sidx_block_pop_hu_the_geom
pg_restore: creating INDEX sidx_county_2010
pg_restore: creating INDEX sidx_place_2012_the_geom
pg_restore: setting owner and privileges for DATABASE census
pg_restore: setting owner and privileges for SCHEMA iowa
pg_restore: setting owner and privileges for SCHEMA nebraska
pg_restore: setting owner and privileges for SCHEMA public
pg_restore: setting owner and privileges for COMMENT SCHEMA public
pg_restore: setting owner and privileges for ACL public
pg_restore: setting owner and privileges for SCHEMA united_states
pg_restore: setting owner and privileges for SCHEMA wisconsin
pg_restore: setting owner and privileges for EXTENSION plpgsql
pg_restore: setting owner and privileges for COMMENT EXTENSION plpgsql
pg_restore: setting owner and privileges for EXTENSION postgis
pg_restore: setting owner and privileges for COMMENT EXTENSION postgis
pg_restore: setting owner and privileges for TABLE block_pop_hu_2010
pg_restore: setting owner and privileges for SEQUENCE block_pop_hu_2010_pk_uid_seq
pg_restore: setting owner and privileges for SEQUENCE OWNED BY block_pop_hu_2010_pk_uid_seq
pg_restore: setting owner and privileges for TABLE block_pop_hu_2010
pg_restore: setting owner and privileges for SEQUENCE block_pop_hu_2010_pk_uid_seq
pg_restore: setting owner and privileges for SEQUENCE OWNED BY block_pop_hu_2010_pk_uid_seq
pg_restore: setting owner and privileges for TABLE state_2010
pg_restore: setting owner and privileges for SEQUENCE state_2010_id_seq
pg_restore: setting owner and privileges for SEQUENCE OWNED BY state_2010_id_seq
pg_restore: setting owner and privileges for TABLE block_pop_hu_2010
pg_restore: setting owner and privileges for SEQUENCE block_pop_hu_pk_uid_seq
pg_restore: setting owner and privileges for SEQUENCE OWNED BY block_pop_hu_pk_uid_seq
pg_restore: setting owner and privileges for TABLE county_2010
pg_restore: setting owner and privileges for SEQUENCE county_2010_id_seq
pg_restore: setting owner and privileges for SEQUENCE OWNED BY county_2010_id_seq
pg_restore: setting owner and privileges for TABLE place_2012
pg_restore: setting owner and privileges for SEQUENCE place_2012_pk_uid_seq
pg_restore: setting owner and privileges for SEQUENCE OWNED BY place_2012_pk_uid_seq
pg_restore: setting owner and privileges for DEFAULT pk_uid
pg_restore: setting owner and privileges for DEFAULT pk_uid
pg_restore: setting owner and privileges for DEFAULT id
pg_restore: setting owner and privileges for DEFAULT pk_uid
pg_restore: setting owner and privileges for DEFAULT id
pg_restore: setting owner and privileges for DEFAULT pk_uid
pg_restore: setting owner and privileges for TABLE DATA block_pop_hu_2010
pg_restore: setting owner and privileges for SEQUENCE SET block_pop_hu_2010_pk_uid_seq
pg_restore: setting owner and privileges for TABLE DATA block_pop_hu_2010
pg_restore: setting owner and privileges for SEQUENCE SET block_pop_hu_2010_pk_uid_seq
pg_restore: setting owner and privileges for TABLE DATA spatial_ref_sys
pg_restore: setting owner and privileges for TABLE DATA state_2010
pg_restore: setting owner and privileges for SEQUENCE SET state_2010_id_seq
pg_restore: setting owner and privileges for TABLE DATA block_pop_hu_2010
pg_restore: setting owner and privileges for SEQUENCE SET block_pop_hu_pk_uid_seq
pg_restore: setting owner and privileges for TABLE DATA county_2010
pg_restore: setting owner and privileges for SEQUENCE SET county_2010_id_seq
pg_restore: setting owner and privileges for TABLE DATA place_2012
pg_restore: setting owner and privileges for SEQUENCE SET place_2012_pk_uid_seq
pg_restore: setting owner and privileges for CONSTRAINT block_pop_hu_2010_pkey
pg_restore: setting owner and privileges for CONSTRAINT block_pop_hu_2010_pkey
pg_restore: setting owner and privileges for CONSTRAINT state_2010_pkey
pg_restore: setting owner and privileges for CONSTRAINT block_pop_hu_pkey
pg_restore: setting owner and privileges for CONSTRAINT county_2010_pkey
pg_restore: setting owner and privileges for CONSTRAINT place_2012_pkey
pg_restore: setting owner and privileges for INDEX sidx_block_pop_hu_2010_the_geom
pg_restore: setting owner and privileges for INDEX sidx_block_pop_hu_2010_the_geom
pg_restore: setting owner and privileges for INDEX sidx_state_2010
pg_restore: setting owner and privileges for INDEX sidx_block_pop_hu_the_geom
pg_restore: setting owner and privileges for INDEX sidx_county_2010
pg_restore: setting owner and privileges for INDEX sidx_place_2012_the_geom
WARNING: errors ignored on restore: 30

closed as off-topic by dezso, Philᵀᴹ, RLF, LowlyDBA, Marcello Miorelli Oct 23 '16 at 12:21

This question appears to be off-topic. The users who voted to close gave this specific reason:

  • "Too localized - this could be because your code has a typo, basic error, or is not relevant to most of our audience. Consider revising your question so that it appeals to a broader audience. As it stands, the question is unlikely to help other users (regarding typo questions, see this meta question for background)." – dezso, Philᵀᴹ, RLF, LowlyDBA, Marcello Miorelli
If this question can be reworded to fit the rules in the help center, please edit the question.

3

I find that when I use the "-Fc" option to pg_dump, I do get SQL statements to create sequences in the resulting dump file.

pg_dump -Fc basted_data -f ./stuff.fc

This is with old (8.4.20) PostgreSQL; I would hope that this behavior is unchanged in later versions.

I was using the custom-formatted dump because, like the many people you encountered in researching your question, I wanted to restore a single table. I found that trying to restore a single table with the pg_restore "-t " option failed because of a missing sequence. But when I dumped out the entire "stuff.fc" backup to plaintext, I was able to search it and find SQL statements that created that sequence. When I cut/pasted them into a plaintext dump of the SQL generated by the "pgrestore -t", I was able to use the result to successfully create and load my single table.

So, maybe pg_dump -Fc will work for you.

2

Answer originally left as a comment by the question author:

I had initially set the server up with the BDR build. I built it long enough ago that I had kinda forgotten about it. Since it restores everything but the sequences I've gone ahead and rebuilt the sequences on the new server (without BDR) and backups appear to be functioning as expected now.

Not the answer you're looking for? Browse other questions tagged or ask your own question.