Обсуждение: [SQL] PostgreSQL 9.3 DB initialization issue

On 05/13/2017 06:11 AM, Ashish Tiwari wrote:
> Hi Team,
>
> Our team has done some activity but they didn't take the backup before
> activity .it was setting of replication.The Pg data has been kept secure.

What data, a dump file or the data directory?

What do you mean by setting of replication?

>
> Now when initializaing the DB getting the error as below.

So you ran initdb to create a cluster?

Or did you do something else?

>
> The database cluster was initialized with PG_CONTROL_VERSION 922, but
> the server was compiled with PG_CONTROL_VERSION 937.

The above would seem to indicate you tried to start a Postgres 9.3
server using a Postgres 9.2 data directory.

>
> Any solution if DB can be upgraded from 9.2 to 9.3.

You are trying to upgrade from Postgres 9.2 to 9.3?

How was Postgres installed?

What OS and version?

>
> Any suggestions are welcome
>
>
> Thanks.
>
> Best regards,
> Ashish


--
Adrian Klaver
adrian.klaver@aklaver.com

On 05/13/2017 06:41 AM, Ashish Tiwari wrote:
> Hi Adrian,
>
> Please find the in line answers.
>
> Thanks.
>
> On May 13, 2017 18:55, "Adrian Klaver" <adrian.klaver@aklaver.com
> <mailto:adrian.klaver@aklaver.com>> wrote:
>
>     On 05/13/2017 06:11 AM, Ashish Tiwari wrote:

>
>     What data, a dump file or the data directory?
>
>
> Pgsql/9.3/main

So you are using the deb packages. The above is for the Postgres 9.3
cluster, where is the data from the Postgres 9.2 cluster?

>
>     What do you mean by setting of replication?
>
>     Enabling the Archive and WAL setting.

So was this done before the upgrade, during, after?
I am just not seeing the connection to the error you reported.

>
>
>         Now when initializaing the DB getting the error as below.
>
>
>     So you ran initdb to create a cluster >
>     Yes  ..

What was the exact command you used.

>
>

>
>
>         The database cluster was initialized with PG_CONTROL_VERSION
>         922, but the server was compiled with PG_CONTROL_VERSION 937.
>
>
>     The above would seem to indicate you tried to start a Postgres 9.3
>     server using a Postgres 9.2 data directory.
>
>
> Yes.it seems right.

That is not possible as you found out. When doing an upgrade between
major versions(see note below) in Postgres you either have to dump the
data from the older version and restore it to the new version or you
need to use pg_upgrade.

*NOTE* Up to Postgres version 9.6.x a major version change was a change
in either of the first two numbers. Starting with the next release
(10.x) only the first number is a major version.


At this point you should have two Postgres clusters on you machine,
Something like:

/var/lib/postgresql/9.2/main/

/var/lib/postgresql/9.3/main/

Is this correct?


>
>
>
>
>         Any solution if DB can be upgraded from 9.2 to 9.3.
>
>
>     You are trying to upgrade from Postgres 9.2 to 9.3?
>
>     How was Postgres installed?
>
>
> Sudo apt-get install
>
>
>     What OS and version?
>
>     Ubuntu 14.04 LTS
>

>
>
>         Thanks.
>
>         Best regards,
>         Ashish
>


--
Adrian Klaver
adrian.klaver@aklaver.com

On 05/13/2017 08:53 AM, Ashish Tiwari wrote:
> Hi Andrian,
>
> The data have kept separate as in
>
> /pgsql/data2 directory.
>
> We are passing this path while starting the server using pg-ctl from bin
> directory as well this data path is given in to postgresql.conf .
>
> So in current situation the binary is as of 9.3 and data is as of 9.2
>
> As an alternative can we install the 9.2 and manually make the up DB is
> it possible Or data is lost ?. finally

If  /pgsql/data2 represents the Postgres 9.2 data directory then the
data is not lost.

I am including information from your previous post in the below.

 > $./initdb  -D /pgsql/9.3/main/data

Are you overriding the default location that Debian uses?:

https://wiki.debian.org/PostgreSql

I would look at the above to see how you can use the Debian tools to
maintain/create multiple Postgres clusters.


 > 9.2 is uninstalled now.

You will need to install it again to be able to access the data in the
9.2 data directory you show above(/pgsql/data2). I would maintain that
copy(/pgsql/data2) and just copy it into the 9.2 cluster when you get it
reinstalled. If you want to upgrade to 9.3, then use the 9.3 pg_dumpall
to dump the 9.2 data and then the 9.3 pg_restore to restore it into the
9.3 instance.


>
> Thanks.
> BR,Ashish
>



--
Adrian Klaver
adrian.klaver@aklaver.com

On 05/13/2017 02:50 PM, Steve Midgley wrote:
>
>

>
> I am by no means an expert on any of this, but Adrian, I think you have
> a typo in your instructions? Should it read:
>
>   "use the *[9.2]* pg_dumpall to dump the 9.2 data and then the 9.3
> pg_restore to restore it into the 9.3 instance." (in the original it
> said [9.3] where my brackets are).
>
> I think you meant to say that v 9.2 server binary should be used to call
> pg_dumpall (and run against the v9.2 running server instance), and then
> the resulting dump file can be read into the 9.3 server by using the
> v9.3 binary for pg_restore. The pg_restore (v9.3) command will operate
> using source files created by pg_dumpall, and load that into 9.3 running
> server instance.

Well pg_dump(all) is backwards compatible, so the 9.3 version can dump
Postgres versions going back to 7.0. I have always been told that using
the newer binary against an older server is better as it will 'know'
about changes in the newer version:

https://www.postgresql.org/docs/9.3/static/upgrading.html

17.6.1. Upgrading Data via pg_dump

"It is recommended that you use the pg_dump and pg_dumpall programs from
the newer version of PostgreSQL, to take advantage of enhancements that
might have been made in these programs. Current releases of the dump
programs can read data from any server version back to 7.0."

That being said, given that it is only a single version jump using the
9.2 version of pg_dump(all) will most likely work without a hitch.

>
> If I'm wrong about this, please correct me. But given the context of
> debugging on this email thread, it seemed like I should point this
> (possible) typo out, so there was no ambiguity for Ashish and his team.
>
> Steve


--
Adrian Klaver
adrian.klaver@aklaver.com