Re: Transfer db from one port to another - Mailing list pgsql-general

From Adrian Klaver
Subject Re: Transfer db from one port to another
Date
Msg-id 567C2C7C.5050609@aklaver.com
Whole thread Raw
In response to Re: Transfer db from one port to another  (Killian Driscoll <killiandriscoll@gmail.com>)
Responses Re: Transfer db from one port to another  (Killian Driscoll <killiandriscoll@gmail.com>)
List pgsql-general
On 12/24/2015 12:03 AM, Killian Driscoll wrote:

>
>
>         yeah, this one from Adrian, at 7:02am PST (Z-0800) this morning....
>
>             Per previous posts you want, whenever possible, to us a
>             newer version
>             of pg_dump to move a database from an older version(9.3) to
>             a newer
>             one(9.4). Therefore you should do your dump and restore
>             using the
>             pg_dump.exe and pg_restore.exe from the Bitanami bin
>             directory. I
>             would cd to the above directory and do:
>
>             pg_dump -V
>             pg_restore -V
>
>             to make sure the programs are found and are the 9.4 versions.
>
>             Then do:
>
>             pg_dump -Fc -p 5432 -U postgres -f irll_project.out irll_project
>
>             pg_restore -U postgres -p 5532 irll_project.out
>
>
>         that last needs to have -d newdbname     where newdbname has already
>         been created, for instance, by...
>
>
>     Aah, my mistake. Yes you need to specify the database to get the
>     restore to work properly. Also explains why there is nothing in the
>     logs.
>
>
> OK - with the inclusion of stating the dbname the restore works, but not
> correctly: what is restored is 24 of 48 tables and 1 of 22 views from
> one schema and no tables from the other schema.
>
> A log appeared at 0:08 last night (I'm at GMT +1), which I've attached.
> Plus, I did the dump and restore again this morning and have attached
> the text from the windows shell if that helps
>


Well the one from this morning shows(I did not look through whole thing)
you restoring over existing database objects. I would say at this point
the  best thing you can do is get to a known state on the 9.4 cluster
you want to dump to. I am assuming you are not doing anything with the
database irll_project on the 9.4 server at this point, correct?

If so, for the below keep track of exactly what you do and the order you
do it, in case you need to post back here.

1) On the 9.4 server, while logged into another database on the server,
say postgres do:

DROP DATABASE irll_project;

2) Using the 9.4 version of pg_dump dump the 9.3 version of irll_project.

3) Using the 9.4 version of pg_restore restore irll_project to the 9.4
server.


--
Adrian Klaver
adrian.klaver@aklaver.com


pgsql-general by date:

Previous
From: Killian Driscoll
Date:
Subject: Re: Transfer db from one port to another
Next
From: Amit Bondwal
Date:
Subject: Re: BDR error while adding 3rd node to cluster