I'm transferring all of the databases on my old postgres server to a new server. To do this I'm using pg_dump and then pg_restore:
pg_dump --host localhost --port 5432 --username "postgres" --format custom --blobs --file ~/backups/census.backup census
--and then--
pg_restore -Cv -h localhost -p 5432 -U postgres -d postgres ./census.backup
The pg_restore gives me a series of errors about sequences not existing. The database is restored with all data intact, but the sequences are not recreated.
The results 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
; 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 a snippet of the relevant pg_restore output (it's rather long):
------------------------------------------------------------------------------------------------------
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: 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;
------------------------------------------------------------------------------------------------------
Am I missing something in my pg_restore command? Thanks for your help.
