Thread: Detaching database
Hi. Is any way how to detach complete database, and attach it into other Postgre server (like MSSQL, Interbase etc. databases movability) ? Moving database via SQL export is crazy way for me (and for my customers). Thanks and forgive me for my poor english. Petr
Pg_dump followed by pg_restore is the usual way:
========================================================
pg_dump dumps a database as a text file or to other formats.
Usage:
pg_dump [OPTION]... [DBNAME]
General options:
-f, --file=FILENAME output file name
-F, --format=c|t|p output file format (custom, tar, plain text)
-i, --ignore-version proceed even when server version mismatches
pg_dump version
-v, --verbose verbose mode
-Z, --compress=0-9 compression level for compressed formats
--help show this help, then exit
--version output version information, then exit
Options controlling the output content:
-a, --data-only dump only the data, not the schema
-c, --clean clean (drop) schema prior to create
-C, --create include commands to create database in dump
-d, --inserts dump data as INSERT, rather than COPY,
commands
-D, --column-inserts dump data as INSERT commands with column
names
-E, --encoding=ENCODING dump the data in encoding ENCODING
-n, --schema=SCHEMA dump the named schema only
-o, --oids include OIDs in dump
-O, --no-owner skip restoration of object ownership
in plain text format
-s, --schema-only dump only the schema, no data
-S, --superuser=NAME specify the superuser user name to use in
plain text format
-t, --table=TABLE dump the named table only
-x, --no-privileges do not dump privileges (grant/revoke)
-X disable-dollar-quoting, --disable-dollar-quoting
disable dollar quoting, use SQL standard
quoting
-X disable-triggers, --disable-triggers
disable triggers during data-only restore
-X use-set-session-authorization, --use-set-session-authorization
use SESSION AUTHORIZATION commands instead of
OWNER TO commands
Connection options:
-h, --host=HOSTNAME database server host or socket directory
-p, --port=PORT database server port number
-U, --username=NAME connect as specified database user
-W, --password force password prompt (should happen
automatically)
If no database name is supplied, then the PGDATABASE environment
variable value is used.
Report bugs to <pgsql-bugs@postgresql.org>.
========================================================
pg_restore restores a PostgreSQL database from an archive created by
pg_dump.
Usage:
pg_restore [OPTION]... [FILE]
General options:
-d, --dbname=NAME connect to database name
-f, --file=FILENAME output file name
-F, --format=c|t specify backup file format
-i, --ignore-version proceed even when server version mismatches
-l, --list print summarized TOC of the archive
-v, --verbose verbose mode
--help show this help, then exit
--version output version information, then exit
Options controlling the restore:
-a, --data-only restore only the data, no schema
-c, --clean clean (drop) schema prior to create
-C, --create create the target database
-I, --index=NAME restore named index
-L, --use-list=FILENAME use specified table of contents for ordering
output from this file
-n, --schema=NAME restore only objects in this schema
-O, --no-owner skip restoration of object ownership
-P, --function=NAME(args)
restore named function
-s, --schema-only restore only the schema, no data
-S, --superuser=NAME specify the superuser user name to use for
disabling triggers
-t, --table=NAME restore named table
-T, --trigger=NAME restore named trigger
-x, --no-privileges skip restoration of access privileges
(grant/revoke)
-X disable-triggers, --disable-triggers
disable triggers during data-only restore
-X use-set-session-authorization, --use-set-session-authorization
use SESSION AUTHORIZATION commands instead of
OWNER TO commands
Connection options:
-h, --host=HOSTNAME database server host or socket directory
-p, --port=PORT database server port number
-U, --username=NAME connect as specified database user
-W, --password force password prompt (should happen
automatically)
-e, --exit-on-error exit on error, default is to continue
If no input file name is supplied, then standard input is used.
Report bugs to <pgsql-bugs@postgresql.org>.
========================================================
What exactly is the problem you are trying to solve? I am guessing that
there is a tool that does exactly what you want if you can describe your
need carefully enough.
Maybe you need Slony or something else like that.
http://gborg.postgresql.org/project/slony1/projdisplay.php
But it is very hard to say from the brief description of what you want.
> -----Original Message-----
> From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-
> owner@postgresql.org] On Behalf Of Petr
> Sent: Tuesday, December 27, 2005 3:40 PM
> To: pgsql-general@postgresql.org
> Subject: [GENERAL] Detaching database
>
> Hi.
>
> Is any way how to detach complete database, and attach it into other
> Postgre
> server (like MSSQL, Interbase etc. databases movability) ?
> Moving database via SQL export is crazy way for me (and for my
customers).
>
> Thanks and forgive me for my poor english.
>
> Petr
>
>
> ---------------------------(end of
broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings
Petr, As long as the new server is the same operating system, and the versions of postgres are the same, you can do a binary copyof the data directory and move it to the new machine, point the new server's postgres to the copied and data and startit up. Indexes, statistics, etc. all are intact and ready to go. On the negative side I think you need to idle the sourcedatabase during the initial copy. We've done this to move databases in the 5-10 gigabyte range on postgres 7.4. I am not familiar with those other database's capabilities so if mu humble suggestion is not what you want, a bit more informationabout the problem might be of help. HTH, Greg Williamson -----Original Message----- From: pgsql-general-owner@postgresql.org on behalf of Petr Sent: Tue 12/27/2005 3:39 PM To: pgsql-general@postgresql.org Cc: Subject: [GENERAL] Detaching database Hi. Is any way how to detach complete database, and attach it into other Postgre server (like MSSQL, Interbase etc. databases movability) ? Moving database via SQL export is crazy way for me (and for my customers). Thanks and forgive me for my poor english. Petr ---------------------------(end of broadcast)--------------------------- TIP 5: don't forget to increase your free space map settings !DSPAM:43b1d0f4175415020319812!
OK. I'm try to explain my problems. My customer have Postgre server with any older version of my DB (without any new or modified views, functions etc.) and when i create export (in pgAdmin3), then pg_dump makes a SQL script. Ok. It's nice, but when i'm trying to run this script on customer's machine, then i have many errors, because any views are not in his database, and script trying to drop it. When i'm set to don't make drop functions (in export), then i have other errors, with existing views (view allready exists). Petr
I am debugging a plpgsql function. I came from an MSSQL server and I am missing a few techniques here so I apologize for using MS as my reference. Is there a variable that stores the number of records the last statement effected? - in MS it was @@RowCount, I could constantly check its value as I stepped through the code Is there a quick way to see a recordset in during the debug (not just the final recordset) - in MS the debugger always displayed the result of Select statements so by inserting one I could check intermediary results. (I am experimenting with EMS SQL Manager now to step through the code). Thanks. SWK
This sounds like a dangerous line to walk, to me. If the schemas for the two database systems are diverging, then trying to synchronize them is sure to have problems. I do not think that there is any miracle cure (using PostgreSQL or any other database system). If you know of a sure subset of tables that are guaranteed not to change, then perhaps you can create a table by table script for those. > -----Original Message----- > From: pgsql-general-owner@postgresql.org [mailto:pgsql-general- > owner@postgresql.org] On Behalf Of Petr > Sent: Tuesday, December 27, 2005 4:00 PM > To: pgsql-general@postgresql.org > Subject: Re: [GENERAL] Detaching database > > OK. > > I'm try to explain my problems. My customer have Postgre server with any > older version of my DB (without any new or modified views, functions etc.) > and when i create export (in pgAdmin3), then pg_dump makes a SQL script. > Ok. > It's nice, but when i'm trying to run this script on customer's machine, > then i have many errors, because any views are not in his database, and > script trying to drop it. When i'm set to don't make drop functions (in > export), then i have other errors, with existing views (view allready > exists). > > Petr > > > ---------------------------(end of broadcast)--------------------------- > TIP 3: Have you checked our extensive FAQ? > > http://www.postgresql.org/docs/faq
On Wed, Dec 28, 2005 at 01:00:27AM +0100, Petr wrote: > I'm try to explain my problems. My customer have Postgre server with any > older version of my DB (without any new or modified views, functions etc.) > and when i create export (in pgAdmin3), then pg_dump makes a SQL script. Ok. > It's nice, but when i'm trying to run this script on customer's machine, > then i have many errors, because any views are not in his database, and > script trying to drop it. When i'm set to don't make drop functions (in > export), then i have other errors, with existing views (view allready > exists). So you get errors because it tries to drop views that don't exist. That's OK, just keep going. Those errors don't break anything they just warn you of something unexpected. Alternativly you could create a new database from your SQL dump and then copy the data you actually want to keep... Have a nice day, -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a > tool for doing 5% of the work and then sitting around waiting for someone > else to do the other 95% so you can sue them.
Attachment
On Tue, Dec 27, 2005 at 15:49:43 -0800, "Gregory S. Williamson" <gsw@globexplorer.com> wrote: > Petr, > > As long as the new server is the same operating system, and the versions of postgres are the same, you can do a binarycopy of the data directory and move it to the new machine, point the new server's postgres to the copied and data andstart it up. Indexes, statistics, etc. all are intact and ready to go. On the negative side I think you need to idle thesource database during the initial copy. As a clarification, 'versions are the same' needs to be more strict than the version number (e.g. 8.1.1) and should include the build options, as some build options (notably --enable-integer-datetimes) change the format used for data.
> > "Gregory S. Williamson" <gsw@globexplorer.com> wrote: > > Petr, > > > > As long as the new server is the same operating system, and the versions of postgres are the same, > <...> > > As a clarification, 'versions are the same' needs to be more strict than the > version number (e.g. 8.1.1) and should include the build options, as some > build options (notably --enable-integer-datetimes) change the format used > for data. Thanks for the clarification on that -- it hadn't occurred tome and I can see how it might lead to issues! G