When I do a pg_dump using PG 9.6, I got this:
Chuck
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.klaver@xxxxxxxxxxx> 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.klaver@xxxxxxxxxxx
> <mailto:adrian.klaver@xxxxxxxxxxx>> 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.klaver@xxxxxxxxxxx <mailto:adrian.klaver@xxxxxxxxxxx>
>
--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx
