Re: Transfer db from one port to another

On 24 December 2015 at 18:33, Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com>
wrote:

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

It worked - thank you very much for your time.

Regarding the file format used: I had used the pg_dump with .sql, but you
suggested .out. Is there a particular reason to use .out instead of .sql
when backing up?

>
>
> --
> Adrian Klaver
> adrian(dot)klaver(at)aklaver(dot)com
>