Re: [GENERAL] Detaching database
On Tue, Dec 27, 2005 at 15:49:43 -0800, Gregory S. Williamson [EMAIL PROTECTED] 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. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Detaching database
Gregory S. Williamson [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] 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, --verboseverbose mode -Z, --compress=0-9 compression level for compressed formats --help show this help, then exit --versionoutput 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, --insertsdump 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-onlydump only the schema, no data -S, --superuser=NAME specify the superuser user name to use in plain text format -t, --table=TABLEdump 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=NAMEconnect 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, --verboseverbose mode --help show this help, then exit --versionoutput 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=NAMErestore only objects in this schema -O, --no-owner skip restoration of object ownership -P, --function=NAME(args) restore named function -s, --schema-onlyrestore 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
Re: [GENERAL] Detaching database
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: [EMAIL PROTECTED] 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! ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
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
Re: [GENERAL] Detaching database
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: [EMAIL PROTECTED] [mailto:pgsql-general- [EMAIL PROTECTED] 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
