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.kla...@aklaver.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.kla...@aklaver.com > > <mailto:adrian.kla...@aklaver.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.kla...@aklaver.com <mailto:adrian.kla...@aklaver.com> > > > > > -- > Adrian Klaver > adrian.kla...@aklaver.com >
