Re: Trouble Upgrading Postgres

When I do a pg_dump using PG 9.6, I got this:

pg_dump: Dumping the contents of table "docfile" failed: PQgetCopyData()
failed.

pg_dump: Error message from server: server closed the connection
unexpectedly

This probably means the server terminated abnormally

before or while processing the request.

pg_dump: The command was: COPY public.docfile (docfile_pkey,
docfileoriginalname, ordernumber, versionnum, docfilecontents, docfilepath,
docfileextension, enddatetime, endby, editnum, insby, insdatetime, modby,
moddatetime, active, doc_fkey) TO stdout;

I've looked and been unable to find where Centos 7, or Postgres 9.6, stores
the path to the config/data directory outside the data/postgresql.conf
file. But I agree there must be something somewhere.

Chuck

On Sat, Nov 3, 2018 at 6:06 PM Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com>
wrote:

> On 11/3/18 2:56 PM, Charles Martin wrote:
>
> Please reply to list also.
> Ccing list.
>
> > Yes, if I could get pg_dump to work, I think I'd be fine. Maybe. But it
> > doesn't.
>
> Post the error you got to the list and we maybe able to help.
> Also which version of Postgres where you using to take the dump?
>
> >
> > I agree that I've confused Postgres, but I don't know how to resolve the
> > confusion. It is complicated by the fact that my original Centos 7
> > install included Postgres 9.2, so those files are hanging around, along
> > with 9.6 and 11.
> >
> > I posted the error messages I got when postgresql.conf had the data
> > directory set to my basebackup data:
> >
> > *postgresql-9.6.service: main process exited, code=exited,
> status=1/FAILURE*
> >
> > *
> > *
> >
> > Not very helpful.
> >
> >
> > systemctl status postgresql-9.6 provided a bit more info:
> >
> > *●*postgresql-9.6.service - PostgreSQL 9.6 database server
> >
> > Loaded: loaded (/usr/lib/systemd/system/postgresql-9.6.service;
> > disabled; vendor preset: disabled)
> >
> > Active: *failed*(Result: exit-code) since Sat 2018-11-03 15:05:30
> > EDT; 15s ago
> >
> > Docs: https://www.postgresql.org/docs/9.6/static/
> >
> > Process: 32570 ExecStart=/usr/pgsql-9.6/bin/postmaster -D ${PGDATA}
> > *(code=exited, status=1/FAILURE)*
> >
> > Process: 32563
> > ExecStartPre=/usr/pgsql-9.6/bin/postgresql96-check-db-dir ${PGDATA}
> > (code=exited, status=0/SUCCESS)
> >
> > Main PID: 32570 (code=exited, status=1/FAILURE)
> >
> >
> > Yet this went away, and PG 9.6 started, when I changed postgresql.conf
> > to point to the new (empty) data directory, which is confusing.
>
> No not confusing. Not that familiar with RPM packaging as I am with the
> Debian/Ubunto packaging. Still if I remember correctly it also allows
> multiple instances of Postgres to run. To do that it has its own system
> of tracking the data directories. Where you created the new data
> directory is obviously where the package scripts expect to find it. The
> pg_basebackup directory is not.
>
> >
> > Chuck
> >
> >
> > On Sat, Nov 3, 2018 at 5:17 PM Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com
> > <mailto:adrian(dot)klaver(at)aklaver(dot)com>> wrote:
> >
> > 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 <mailto:adrian(dot)klaver(at)aklaver(dot)com>
> >
>
>
> --
> Adrian Klaver
> adrian(dot)klaver(at)aklaver(dot)com
>