Upgrade: 9.0.5->9.3.3

Rich Shepard <rshepard@appl-ecosys.com> writes:

Now, -9.0.5 is installed in /usr/local/pgsql/ and -9.3.3 is installed in
/opt/pgsql-9.3.3. I want to use pg_upgrade and have read the Web page with
the instructions.

I'm guessing from those path names that you are using self-compiled
executables, not somebody's packaging? If the latter, whose? I'm
confused as to how you got pg_upgrade installed without knowing where
it came from.

Instruction #4 tells me to install pg_upgrade and pg_upgrade_support. I
have the 9.0.5 version of pg_upgrade in /opt/postgresql-9.0.5/contrib/
(without pg_upgrade_support). But, I do not see the 'contrib' page on
postgresql.org. I find the 'community' and 'foundry' pages, but not the
'contrib' page. A pointer to pg_upgrade and pg_upgrade_support for 9.3.3 is
needed.

contrib is part of the main PG distribution. The sources for pg_upgrade
would be under contrib/pg_upgrade/ in an unpacked tarball, and
pg_upgrade_support lives next door in contrib/pg_upgrade_support/.
If you did a "make install" in contrib/ that would've installed both.

Possibly you're expecting pg_upgrade_support to produce an executable
under $installdir/bin? It doesn't --- it should get installed as
lib/pg_upgrade_support.so or equivalent. In any case, pg_upgrade will
certainly complain if it can't find it.

If my reading of the instructions is correct, the following command line
should migrate my few, small databased from 9.0.5 to 9.3.3:

pg_upgrade -b /usr/local/pgsql/bin -B /opt/pgsql-9.3.3/bin -d \
/usr/local/pgsql/data -D /opt/pgsql-9.3.3/data

That's pretty much the base case. If none of the other options appeal to
you, don't use 'em.

regards, tom lane

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On Mon, 17 Mar 2014, Tom Lane wrote:

I'm guessing from those path names that you are using self-compiled
executables, not somebody's packaging? If the latter, whose? I'm
confused as to how you got pg_upgrade installed without knowing where it
came from.

Tom,

Both were buit with scripts from SlackBuilds.org. The 9.0.5 pg_upgrade in
/opt/postgresql-9.0.5/contrib/ is datestamped Sep 22, 2011; I don't see that
I actually built and installed it or pg_upgrade_support (that's also there
as source, but neither built nor installed). Three-and-a-half years later
I've no recollection from where I downloaded them.

contrib is part of the main PG distribution. The sources for pg_upgrade
would be under contrib/pg_upgrade/ in an unpacked tarball, and
pg_upgrade_support lives next door in contrib/pg_upgrade_support/. If you
did a "make install" in contrib/ that would've installed both.

A-ha! Running 'pg_upgrade --help' tells me that it is, indeed, installed.
And, it is the version for -9.3.3, too. So, this question's been answered.

That's pretty much the base case. If none of the other options appeal to
you, don't use 'em.

Nah, nothing looks like I need them.

Much appreciated,

Rich

--
Richard B. Shepard, Ph.D. | Have knowledge, will travel.
Applied Ecosystem Services, Inc. |
www.appl-ecosys.com Voice: 503-667-4517 Fax: 503-667-8863

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On Mon, 17 Mar 2014, Rich Shepard wrote:

Now, -9.0.5 is installed in /usr/local/pgsql/ and -9.3.3 is installed in
/opt/pgsql-9.3.3. I want to use pg_upgrade and have read the Web page with
the instructions.

I am having problems initializing the new version in /opt/pgsql-9.3.3. I
kill the postgres-9.0.5 process, and as user 'postgres' I tried to initdb
with this result:

initdb -D /opt/pgsql-9.3.3/9.3/data/
The program "postgres" was found by "/bin/initdb"
but was not the same version as initdb.
Check your installation.

When I ask which postgres I find:

[root@salmo ~]# ll /usr/bin/postgres
lrwxrwxrwx 1 root root 34 Mar 17 06:50 /usr/bin/postgres ->
../lib/postgresql/9.3/bin/postgres*

so I am confused on how I need to run initdb, or what I need to
rename/remove to initialize the new cluster so I can run pg_upgrade and move
the databases over.

Rich

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Rich Shepard <rshepard@appl-ecosys.com> writes:

On Mon, 17 Mar 2014, Rich Shepard wrote:

Now, -9.0.5 is installed in /usr/local/pgsql/ and -9.3.3 is installed in
/opt/pgsql-9.3.3. I want to use pg_upgrade and have read the Web page with
the instructions.

I am having problems initializing the new version in /opt/pgsql-9.3.3. I
kill the postgres-9.0.5 process, and as user 'postgres' I tried to initdb
with this result:

initdb -D /opt/pgsql-9.3.3/9.3/data/
The program "postgres" was found by "/bin/initdb"
but was not the same version as initdb.
Check your installation.

When I ask which postgres I find:

[root@salmo ~]# ll /usr/bin/postgres
lrwxrwxrwx 1 root root 34 Mar 17 06:50 /usr/bin/postgres ->
../lib/postgresql/9.3/bin/postgres*

Your PATH seems to be finding initdb in /bin (or is that /usr/bin),
not the one you want under /opt/pgsql-9.3.3.

regards, tom lane

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On Tue, 18 Mar 2014, Tom Lane wrote:

Your PATH seems to be finding initdb in /bin (or is that /usr/bin), not
the one you want under /opt/pgsql-9.3.3.

Tom,

Thanks for catching what I did not see. There was an initdb from 2011 in
/bin/ and the new one in /usr/bin/ is a softlink to
../lib/postgresql/9.3/bin/initdb*

Removing the older initdb allowed the new one to run and successfully
complete.

Regards,

Rich

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Postgresql-9.0.5 is installed in /usr/local/pgsql/ and -9.4's data/
subdirectory is installed in /var/lib/pgsql/9.4/ with the executable in
/usr/bin/.

Running pg_upgrade fails at the end because of a locale difference:

lc_ctype cluster values do not match: old "C", new "en_US.UTF-8"
Failure, exiting

How do I remedy this?

Thanks in advance,

Rich

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On Fri, Mar 21, 2014 at 9:34 AM, Rich Shepard <rshepard@appl-ecosys.com> wrote:

Postgresql-9.0.5 is installed in /usr/local/pgsql/ and -9.4's data/
subdirectory is installed in /var/lib/pgsql/9.4/ with the executable in
/usr/bin/.

Running pg_upgrade fails at the end because of a locale difference:

lc_ctype cluster values do not match: old "C", new "en_US.UTF-8"
Failure, exiting

How do I remedy this?

Drop your newly created 9.4 cluster dir and re-init it with C locale like this:

initdb --locale=C -D ...

--
Kind regards,
Sergey Konoplev
PostgreSQL Consultant and DBA

http://www.linkedin.com/in/grayhemp
+1 (415) 867-9984, +7 (901) 903-0499, +7 (988) 888-1979
gray.ru@gmail.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On Fri, 21 Mar 2014, Sergey Konoplev wrote:

Drop your newly created 9.4 cluster dir and re-init it with C locale like this:

Sergey,

How do I drop the cluster? I've not had need to do this before.

initdb --locale=C -D ...

Do I want the former locale or the current one; does it make any
difference for the few databases in the cluster?

Thanks,

Rich

--
Richard B. Shepard, Ph.D. | Have knowledge, will travel.
Applied Ecosystem Services, Inc. |
www.appl-ecosys.com Voice: 503-667-4517 Fax: 503-667-8863

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On 03/21/2014 09:34 AM, Rich Shepard wrote:

Postgresql-9.0.5 is installed in /usr/local/pgsql/ and -9.4's data/
subdirectory is installed in /var/lib/pgsql/9.4/ with the executable in
/usr/bin/.

Running pg_upgrade fails at the end because of a locale difference:

lc_ctype cluster values do not match: old "C", new "en_US.UTF-8"
Failure, exiting

How do I remedy this?

If you want to use pg_upgrade, then you are going to have to dump your
old database, clean it to UTF-8 (with say iconv), then reimport it.

At that point you might as well just pull it into 9.3.

JD

Thanks in advance,

Rich

--
Command Prompt, Inc. - http://www.commandprompt.com/ 509-416-6579
PostgreSQL Support, Training, Professional Services and Development
High Availability, Oracle Conversion, Postgres-XC, @cmdpromptinc
Political Correctness is for cowards.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On Fri, 21 Mar 2014, Joshua D. Drake wrote:

If you want to use pg_upgrade, then you are going to have to dump your old
database, clean it to UTF-8 (with say iconv), then reimport it.

At that point you might as well just pull it into 9.3.

Joshua,

I did a pg_dumpall before starting this process. If I can use pg_restore
on the running 9.4 will that convert locales and get the job done?

Thanks,

Rich

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On Fri, 21 Mar 2014, Rich Shepard wrote:

I did a pg_dumpall before starting this process. If I can use pg_restore
on the running 9.4 will that convert locales and get the job done?

Let me be more specific. Can I get databases working again by doing these
steps?

1.) Start the new -9.4 version running.

2.) Run pg_restore on each database that's in the .sql file created by
pg_dumpall.

I have 5 databases in the file, but only need to restore 4 to fully
working condition.

If there's another process please tell me how to proceed.

Thanks,

Rich

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On Fri, 21 Mar 2014, Rich Shepard wrote:

1.) Start the new -9.4 version running.

It's now running.

2.) Run pg_restore on each database that's in the .sql file created by
pg_dumpall.

Will this work?

psql -e -d template1 -f
/home/rshepard/data/database-backups/pg-9.0.5-2014-03.17.sql

(all on one line, of course; wrapped by alpine.)

Rich

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On Fri, 21 Mar 2014, Rich Shepard wrote:

psql -e -d template1 -f
/home/rshepard/data/database-backups/pg-9.0.5-2014-03-17.sql

Yep.

Now all databases are running on 9.3.4.

Thanks, all!

Rich

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general