Thread: 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:

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