Thread: Backup and restore sequences
PostgreSQL 9.0.1/pgAdminIII 1.12.1
I want to copy selected tables from one database to another and maintain the sequences which I originally setup with:
CREATE SEQUENCE venues_id_seq START WITH 1122;
ALTER TABLE venues ALTER COLUMN id SET DEFAULT nextval('venues_id_seq');
... along with their current values, which have been augmented since the database was setup. When I backup via
pgAdminIIIthe sequences are not even included. I also can't find anything in:
man pg_dump
... which specifies sequences.
gvim
On Friday 07 January 2011 7:46:31 am gvim wrote:
> PostgreSQL 9.0.1/pgAdminIII 1.12.1
>
> I want to copy selected tables from one database to another and maintain
> the sequences which I originally setup with:
>
> CREATE SEQUENCE venues_id_seq START WITH 1122;
> ALTER TABLE venues ALTER COLUMN id SET DEFAULT nextval('venues_id_seq');
>
> ... along with their current values, which have been augmented since the
> database was setup. When I backup via pgAdminIII the sequences are not even
> included. I also can't find anything in:
>
> man pg_dump
>
> ... which specifies sequences.
>
> gvim
Details below. When you dump a specific table using -t it will not
automatically dump dependent objects. -t can be used to dump a sequence because
they are just a special type of table.
From:
http://www.postgresql.org/docs/9.0/interactive/app-pgdump.html
"
-t table
--table=table
Dump only tables (or views or sequences) matching table. Multiple tables can
be selected by writing multiple -t switches. Also, the table parameter is
interpreted as a pattern according to the same rules used by psql's \d commands
(see Patterns), so multiple tables can also be selected by writing wildcard
characters in the pattern. When using wildcards, be careful to quote the
pattern if needed to prevent the shell from expanding the wildcards.
The -n and -N switches have no effect when -t is used, because tables
selected by -t will be dumped regardless of those switches, and non-table
objects will not be dumped.
Note: When -t is specified, pg_dump makes no attempt to dump any other
database objects that the selected table(s) might depend upon. Therefore, there
is no guarantee that the results of a specific-table dump can be successfully
restored by themselves into a clean database.
Note: The behavior of the -t switch is not entirely upward compatible
with pre-8.2 PostgreSQL versions. Formerly, writing -t tab would dump all
tables named tab, but now it just dumps whichever one is visible in your
default search path. To get the old behavior you can write -t '*.tab'. Also,
you must write something like -t sch.tab to select a table in a particular
schema, rather than the old locution of -n sch -t tab. "
--
Adrian Klaver
adrian.klaver@gmail.com
On January 7, 2011, gvim <gvimrc@gmail.com> wrote:
> PostgreSQL 9.0.1/pgAdminIII 1.12.1
>
> I want to copy selected tables from one database to another and maintain
> the sequences which I originally setup with:
>
> CREATE SEQUENCE venues_id_seq START WITH 1122;
> ALTER TABLE venues ALTER COLUMN id SET DEFAULT nextval('venues_id_seq');
>
> ... along with their current values, which have been augmented since the
> database was setup. When I backup via pgAdminIII the sequences are not
> even included. I also can't find anything in:
>
> man pg_dump
>
> ... which specifies sequences.
>
> gvim
--table=table
Dump only tables (or views or sequences) matching table. Multiple tables can be selected
by writing multiple -t switches. Also, the table parameter is interpreted as a pattern
according to the same rules used by psql’s \d commands (see Patterns [psql(1)]), so multi-
ple tables can also be selected by writing wildcard characters in the pattern. When using
wildcards, be careful to quote the pattern if needed to prevent the shell from expanding
the wildcards.
--
A hybrid Escalade is missing the point much in the same way that having a diet soda with your extra large pepperoni pizza is missing the point.
On 01/07/2011 08:26 AM, gvim wrote: > On 07/01/2011 15:58, Adrian Klaver wrote: >> >> Details below. When you dump a specific table using -t it will not >> automatically dump dependent objects. -t can be used to dump a >> sequence because >> they are just a special type of table. >> >> From: >> http://www.postgresql.org/docs/9.0/interactive/app-pgdump.html >> " >> -t table >> --table=table >> > > Thanks. I just found this and it works great but wish pgAdmin had > something similar. > > gvim I broke down and installed pgAdmin. You can do what you want by finding the sequence in the object browser on the left and right clicking and selecting CREATE script. -- Adrian Klaver adrian.klaver@gmail.com