PostgreSQL Discussion - Detaching database

Author

Detaching database

Petr

2005-12-27, 8:24 pm

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

Dann Corbit

2005-12-27, 8:24 pm

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...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

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

Gregory S. Williamson

2005-12-27, 8:24 pm

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 copy of the data directory and move it to the new machine, point the new server's postgres to the copied and data and start it up. Index
es, statistics, etc. all are intact and ready to go. On the negative side I think you need to idle the source database 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 information about 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:43b1d0f417541
5020319812!

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql
.org so that your
message can get through to the mailing list cleanly

Petr

2005-12-27, 8:24 pm

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

Dann Corbit

2005-12-27, 8:24 pm

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

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

Martijn van Oosterhout

2005-12-28, 3:23 am

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.

Bruno Wolff III

2005-12-29, 3:24 am

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 binary copy of the data directory and move it to the new machine, point the new server's postgres to the copied and data and start it up. Ind
exes, statistics, etc. all are intact and ready to go. On the negative side I think you need to idle the source 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.

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

Gregory S. Williamson

2005-12-29, 3:24 am

>
> "Gregory S. Williamson" <gsw@globexplorer.com> wrote:
> <...>
>
> 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

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

Sponsored Links