Обсуждение: Export and import from one postgres server to another
Hi, I am trying to "simply" export my postgres database from one server and then import it into another. I thought I could usePhpPgAdmin, but the hints on the web don't seem to work. One said to simply get a dump from one phpPgAdmin, the go tothe other server, select the database, click the SQL button and then select the dump file. When I do this the import failsbecause the dump file begins with a \connect statement. The escape char doesn't seem to go through the web frontendor something. But without the connect statement it ends up telling me no database selected. Please, can someone letme know how to do this? Thanks Alex
Вложения
On Mon, May 28, 2012 at 10:27 PM, Alexander Reichstadt <info@apfeltaste.net> wrote: > I am trying to "simply" export my postgres database from one server and then import it into another. I thought I coulduse PhpPgAdmin, but the hints on the web don't seem to work. One said to simply get a dump from one phpPgAdmin, thego to the other server, select the database, click the SQL button and then select the dump file. When I do this the importfails because the dump file begins with a \connect statement. The escape char doesn't seem to go through the web frontendor something. But without the connect statement it ends up telling me no database selected. Please, can someone letme know how to do this? This will help you http://www.postgresql.org/docs/9.1/static/app-pgdump.html -- Sergey Konoplev a database and software architect http://www.linkedin.com/in/grayhemp Jabber: gray.ru@gmail.com Skype: gray-hemp Phone: +79160686204
On 05/28/2012 11:27 AM, Alexander Reichstadt wrote: > Hi, > > I am trying to "simply" export my postgres database from one server and then import it into another. I thought I could use PhpPgAdmin, but the hints on the web don't seem to work. One said to simply get a dump from one phpPgAdmin, the go to the other server, select the database, click the SQL button and then select the dump file. When I do this the import fails because the dump file begins with a \connect statement. The escape char doesn't seem to go through the web frontend or something. But without the connect statement it ends up telling me no database selected. Please, can someone let me know how to do this? From the phpPgAdmin FAQ: https://raw.github.com/phppgadmin/phppgadmin/master/FAQ Questions on dumps ------------------ Q: Why can't I reload the SQL script I dumped in the SQL window? A: The following limitations currently exist in SQL script execution: * Only uploaded SQL scripts can contain COPY commands and for this to work, you must have PHP 4.2 or higher. * 'psql' commands such as '\connect' will not work at all. * Multiline SQL statements will not work, eg: CREATE TABLE example ( a INTEGER ); * You cannot change the current database or current user during the execution of the script. We do intend to work on some of these limitations in the future, but some of them are Postgres restrictions and we recommend using the 'psql' utility to restore your full SQL dumps. > > Thanks > Alex -- Adrian Klaver adrian.klaver@gmail.com
On 29 May 2012 14:58, Adrian Klaver <adrian.klaver@gmail.com> wrote: > Q: Why can't I reload the SQL script I dumped in the SQL window? > > A: The following limitations currently exist in SQL script execution: > > * 'psql' commands such as '\connect' will not work at all. Wait a minute! They use the pg_dump utility to create the dump file (there are \connect statements and such in it, after all), but they don't use psql/pg_restore to restore? -- If you can't see the forest for the trees, Cut the trees and you'll see there is no forest.
On Mon, May 28, 2012 at 1:27 PM, Alexander Reichstadt <info@apfeltaste.net> wrote: > Hi, > > I am trying to "simply" export my postgres database from one server and then import it into another. I thought I coulduse PhpPgAdmin, but the hints on the web don't seem to work. One said to simply get a dump from one phpPgAdmin, thego to the other server, select the database, click the SQL button and then select the dump file. When I do this the importfails because the dump file begins with a \connect statement. The escape char doesn't seem to go through the web frontendor something. But without the connect statement it ends up telling me no database selected. Please, can someone letme know how to do this? command line tools and simple scripting are the way to do this. it works equally well on *nix and windows. a simple script might be: psql -h restoredb -c "drop database if exists mydb" psql -h restoredb -c "create database mydb" pg_dump -h dumpdb | psql restoredb you'll probably want to add some logging and error checking (this will work differently depending on o/s) but that's the basic mechanism. Once you have it scripted, it then becomes a matter of scheduling the backup and restore. Another fancier approach to dealing with this is to look at setting up replication. merlin
On 05/29/2012 02:27 AM, Alexander Reichstadt wrote:
I don't really know why pg_dump includes that \connect , but it isn't the only problem you'll hit with that approach. PgAdmin-III seems to wrap the invoked SQL in a transaction, and the dump will try to (re)-create the `postgres' role, which will fail and abort the transaction.
The usual way to dump and restore is to use `pg_dump -Fc -f mydb.backup mydb' when backing up, then either PgAdmin-III's "Restore" dialog or `pg_restore --dbname mydb mydb.backup' when restoring. You'll have to create the database prior to restoring to it.
I wrote a bit of a grumble about the usability issues around dump/reload especially between *nix and Windows recently:
http://blog.ringerc.id.au/2012/05/postgresql-usability-pgadmin-iii-and-pg.html
and would be interested in your comments/experiences because more knowledge will help produce a better UI if I ever get the time to have a go at addressing some of this, or if someone else does.
--
Craig Ringer
Hi, I am trying to "simply" export my postgres database from one server and then import it into another. I thought I could use PhpPgAdmin, but the hints on the web don't seem to work. One said to simply get a dump from one phpPgAdmin, the go to the other server, select the database, click the SQL button and then select the dump file. When I do this the import fails because the dump file begins with a \connect statement.
I don't really know why pg_dump includes that \connect , but it isn't the only problem you'll hit with that approach. PgAdmin-III seems to wrap the invoked SQL in a transaction, and the dump will try to (re)-create the `postgres' role, which will fail and abort the transaction.
The usual way to dump and restore is to use `pg_dump -Fc -f mydb.backup mydb' when backing up, then either PgAdmin-III's "Restore" dialog or `pg_restore --dbname mydb mydb.backup' when restoring. You'll have to create the database prior to restoring to it.
I wrote a bit of a grumble about the usability issues around dump/reload especially between *nix and Windows recently:
http://blog.ringerc.id.au/2012/05/postgresql-usability-pgadmin-iii-and-pg.html
and would be interested in your comments/experiences because more knowledge will help produce a better UI if I ever get the time to have a go at addressing some of this, or if someone else does.
--
Craig Ringer
On 05/29/2012 06:56 PM, Craig Ringer wrote: > On 05/29/2012 02:27 AM, Alexander Reichstadt wrote: >> Hi, >> >> I am trying to "simply" export my postgres database from one server and then import it into another. I thought I coulduse PhpPgAdmin, but the hints on the web don't seem to work. One said to simply get a dump from one phpPgAdmin, thego to the other server, select the database, click the SQL button and then select the dump file. When I do this the importfails because the dump file begins with a \connect statement. > > I don't really know why pg_dump includes that \connect , but it isn't > the only problem you'll hit with that approach. PgAdmin-III seems to > wrap the invoked SQL in a transaction, and the dump will try to > (re)-create the `postgres' role, which will fail and abort the transaction. Well just to keep the confusion level down a bit, the OP was referring to phpPgAdmin:) > > -- > Craig Ringer -- Adrian Klaver adrian.klaver@gmail.com
On 05/30/2012 10:01 AM, Adrian Klaver wrote: > On 05/29/2012 06:56 PM, Craig Ringer wrote: >> On 05/29/2012 02:27 AM, Alexander Reichstadt wrote: >>> Hi, >>> >>> I am trying to "simply" export my postgres database from one server >>> and then import it into another. I thought I could use PhpPgAdmin, >>> but the hints on the web don't seem to work. One said to simply get >>> a dump from one phpPgAdmin, the go to the other server, select the >>> database, click the SQL button and then select the dump file. When I >>> do this the import fails because the dump file begins with a >>> \connect statement. >> >> I don't really know why pg_dump includes that \connect , but it isn't >> the only problem you'll hit with that approach. PgAdmin-III seems to >> wrap the invoked SQL in a transaction, and the dump will try to >> (re)-create the `postgres' role, which will fail and abort the >> transaction. > > Well just to keep the confusion level down a bit, the OP was referring > to phpPgAdmin:) Argh, I missed that. Thanks very much for spotting and correcting it. -- Craig Ringer
On Wed, 2012-05-30 at 09:56 +0800, Craig Ringer wrote: > On 05/29/2012 02:27 AM, Alexander Reichstadt wrote: > > Hi, > > > > I am trying to "simply" export my postgres database from one server and then import it into another. I thought I coulduse PhpPgAdmin, but the hints on the web don't seem to work. One said to simply get a dump from one phpPgAdmin, thego to the other server, select the database, click the SQL button and then select the dump file. When I do this the importfails because the dump file begins with a \connect statement. > > I don't really know why pg_dump includes that \connect , but it isn't > the only problem you'll hit with that approach. PgAdmin-III seems to > wrap the invoked SQL in a transaction, and the dump will try to > (re)-create the `postgres' role, which will fail and abort the transaction. > While I know the thread isn't on pgAdmin, I still want to fix an error. pgAdmin doesn't wrap the SQL in a transaction (in the query tool, which is the only tool that allows you to run a query). -- Guillaume http://blog.guillaume.lelarge.info http://www.dalibo.com