Re: Trouble Upgrading Postgres

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(dot)klaver(at)aklaver(dot)com
> <mailto: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>
> > <mailto: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>
> <mailto:adrian(dot)klaver(at)aklaver(dot)com <mailto:adrian(dot)klaver(at)aklaver(dot)com>>
> >
>
>
> --
> 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