Re: Trouble Upgrading Postgres

On 11/3/18 12:57 PM, Charles Martin wrote:
> I'd be grateful for some help. I am trying to move a large database from
> PostgreSQL 9.6 on Centos 6 to a different server using PostgreSQL 11 on
> Centos 7. I can't do a pg_dump because it always fails on the largest
> table.

I would answer Ron's question on this first as solving it would be the
easiest fix.

>So tried to do pb_basebackup and copy that to the new PG 11
> server. Except that pg_upgrade expects the new and old versions of PG to
> be side-by-side. So I installed 9.6 on the new server, ran initdb,

The is probably the issue, you now have two 9.6 data directory
instances, the one you created with initdb and the one that came over
with pg_basebackup. I am guessing the editing below has left the server
in a confused state about which directory to use. The error messages you
got when trying to restart the server would be helpful.

> verified that it started, then stopped it and edited postgresql.conf
> data path to the location of the pg_basebackup files. Then 9.6 would no
> longer start. So how can I get my PG 9.6 data into a new PG 11 database?
>
> Probably related to my troubles are my attempts to get replication set
> up. But before I dive back into that, I thought I'd better try getting
> my 9.6 data into the new 9.6 server, then run PG 11's pg_upgrade and
> mount the data in PG 11. Then maybe I can get replication started.
>
> I've read that logical replication can be used to migrate from 9.6 to
> 11, but haven't found any documentation on doing that.
>
> Chuck Martin

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