On 11/3/18 3:47 PM, Charles Martin wrote:
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
Is this error the client reporting?
Is this the same that is showing up in the server log?
This probably means the server terminated abnormally
So where is the server located relative to the pg_dump client?
On the same machine?
If so is it a virtual machine e.g AWS?
Across a local or remote network?
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
<mailto: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>
> <mailto: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>
<mailto:adrian.klaver@xxxxxxxxxxx <mailto:adrian.klaver@xxxxxxxxxxx>>
>
--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx <mailto:adrian.klaver@xxxxxxxxxxx>
--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx
