Detaching database

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

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. Indexes, 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: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)---------------------------

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/

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

"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