Thread: Upgrade: 9.0.5->9.3.3
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. 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. 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 I don't see that I need any additional options. Do I? TIA, Rich
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
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
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
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
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
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
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
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.
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
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
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
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
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