Re: Trouble Upgrading Postgres - Mailing list pgsql-general

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


-- 
Adrian Klaver
adrian.klaver@aklaver.com