Re: Upgrade Problem: 7.4.3 -> 8.1.2

It's time for me to stop procrastinating and upgrade postgres from -7.4.3
to -8.1.2. Unfortunately, I think that I got it wrong. Here's what I did on
my Slackware-10.2 box:

1) cd to /usr/local/pgsql
2) su'd to postgres
3) ran 'pg_dumpall'
4) as root, ran 'upgradepkg slackware-8.1.2*.tgz'
5) as root, ran '/etc/rc.d/rc.postgresql stop'
6) as root, ran '/etc/rc.d/rc.postgresql start'; yes, there's a postmaster
process running.
7) back to /usr/local/pgsql; as postgres ran 'pg_restore'
8) I see nothing in the virtual terminal. The cursor sits on the following
line.

The only live data I have are the accounting data from SQL-Ledger. It
shouldn't take that long to restore it to the new version's directories.

What did I do wrong, and (most important!) how do I recover my data so I
can both continue to work in SL and get another web-based app running with
the postgres back end?

TIA,

Rich

--
Richard B. Shepard, Ph.D. | Author of "Quantifying Environmental
Applied Ecosystem Services, Inc. (TM) | Impact Assessments Using Fuzzy Logic"
<http://www.appl-ecosys.com> Voice: 503-667-4517 Fax: 503-667-8863

Rich Shepard <rshepard(at)appl-ecosys(dot)com> writes:

> 7) back to /usr/local/pgsql; as postgres ran 'pg_restore'
> 8) I see nothing in the virtual terminal. The cursor sits on the following
> line.

Did you tell pg_restore to read from a file? Otherwise it will try to
read from your terminal, which probably isn't what you want.

-Doug

On Thu, 19 Jan 2006, Doug McNaught wrote:

> Did you tell pg_restore to read from a file? Otherwise it will try to read
> from your terminal, which probably isn't what you want.

Doug,

No, I didn't. I've no idea where the file was dumped, so I've no idea of
the name or location. Reading in Douglas*2 PostgreSQL book (pages 872-873),
they write nothing about giving pg_dumpall a file name/location nor that
pg_restore needs that information. They do write that pg_restore wants a dump
produced with -format=c or -format=t, without further explanation.

Thanks,

Rich

--
Richard B. Shepard, Ph.D. | Author of "Quantifying Environmental
Applied Ecosystem Services, Inc. (TM) | Impact Assessments Using Fuzzy Logic"
<http://www.appl-ecosys.com> Voice: 503-667-4517 Fax: 503-667-8863

Rich Shepard <rshepard(at)appl-ecosys(dot)com> writes:

> On Thu, 19 Jan 2006, Doug McNaught wrote:
>
>> Did you tell pg_restore to read from a file? Otherwise it will try to read
>> from your terminal, which probably isn't what you want.
>
> Doug,
>
> No, I didn't. I've no idea where the file was dumped, so I've no idea of
> the name or location. Reading in Douglas*2 PostgreSQL book (pages 872-873),
> they write nothing about giving pg_dumpall a file name/location nor that
> pg_restore needs that information. They do write that pg_restore wants a dump
> produced with -format=c or -format=t, without further explanation.

pg_dumpall writes to standard output, so you should have seen an
enormous spew of data at your terminal. I'm surprised you didn't.
The manpages in the official Postgres documentation are quite clear
about the behavior of these utilities; your book doesn't seem to be.

The recommended way to do an upgrade (if you compile from source) is:

1) Compile the new version of PG and install it to a different place.
2) Use the new pg_dump to connect to the old server and dump all the
databases.
3) Make sure your PATH points to the new binaries (or run them by
hand), and
4) Run initdb to create a new data directory in a different place fom
the old one.
5) Stop the old server and start the new one.
5) If you've used pg_dumpall, your backup will be in SQL test format,
so you feed it to 'psql'
6) If you've done pg_dump --format=c for each database, then you need
to use pg_restore. pg_restore is only for the binary format
dumps.

This preserves the old data and binaries so you can back out if you
need to. Naturally you need enough disk space for three copies of the
data.

Now, since you're using a distribution's packages and upgrade
procedure, it's not clear what happened to your data. You might want
to look at the server logfiles (if any) and ask on the Slackware
mailing lists to see if anyone else has had this problem.

-Doug

On Thu, 19 Jan 2006, Doug McNaught wrote:

> Did you tell pg_restore to read from a file? Otherwise it will try to
> read from your terminal, which probably isn't what you want.

Doug,

Here's what I have in /var/lib/:

drwxr-x--- 3 postgres postgres 104 2006-01-19 12:49 pgsql/
drwx------ 3 postgres root 72 2006-01-19 12:47 postgresql/

In pgsql/ there is a data/ directory which contains:

PG_VERSION pg_clog/ pg_multixact/ pg_twophase/
base/ pg_hba.conf pg_subtrans/ pg_xlog/
global/ pg_ident.conf pg_tblspc/ postgresql.conf

and PG_VERSION holds '8.1', with all directories and files having a
creation date of 2006-01-19.

In postgresql/ there is a data/ directory which contains:

PG_VERSION global/ pg_hba.conf pg_xlog/
base/ pg_clog/ pg_ident.conf postgresql.conf

This PG_VERSION also holds '8.1', but all the directories and files have a
creation date of 2004-12-05.

So, I assume that /var/lib/postgresql/data holds the 7.4.3 data files and
/var/lib/postgresql/data holds what should be the 8.1.2 data files.

What specific steps should I take to get the data into the new version so
that SQL-Ledger runs once again? Once I learn this it will be much easier for
me to keep current.

Thanks very much,

Rich

--
Richard B. Shepard, Ph.D. | Author of "Quantifying Environmental
Applied Ecosystem Services, Inc. (TM) | Impact Assessments Using Fuzzy Logic"
<http://www.appl-ecosys.com> Voice: 503-667-4517 Fax: 503-667-8863

On Thu, 19 Jan 2006, Doug McNaught wrote:

> pg_dumpall writes to standard output, so you should have seen an enormous
> spew of data at your terminal. I'm surprised you didn't. The manpages in
> the official Postgres documentation are quite clear about the behavior of
> these utilities; your book doesn't seem to be.

Doug,

Well, it was not an enormous spew, but the data did scroll by on the
terminal.

> This preserves the old data and binaries so you can back out if you need
> to. Naturally you need enough disk space for three copies of the data.

That's not an issue. Small data sets and sufficient disk space.

> Now, since you're using a distribution's packages and upgrade procedure,
> it's not clear what happened to your data. You might want to look at the
> server logfiles (if any) and ask on the Slackware mailing lists to see if
> anyone else has had this problem.

I did write to the package's author, too. I can restore the old version
from the backup tape if need be.

Well, so much for the book. It did seem to be rather sparse on the upgrade.
Hmmm-m-m. Wonder what is the most efficient way to get going again. Think
I'll try the 'pg_dumpall -format=c' from the old directory and see if there's
a new file there.

Many thanks,

Rich

--
Richard B. Shepard, Ph.D. | Author of "Quantifying Environmental
Applied Ecosystem Services, Inc. (TM) | Impact Assessments Using Fuzzy Logic"
<http://www.appl-ecosys.com> Voice: 503-667-4517 Fax: 503-667-8863

Rich Shepard <rshepard(at)appl-ecosys(dot)com> writes:

> Well, so much for the book. It did seem to be rather sparse on the upgrade.
> Hmmm-m-m. Wonder what is the most efficient way to get going again. Think
> I'll try the 'pg_dumpall -format=c' from the old directory and see if there's
> a new file there.

I don't think pg_dumpall supports the 'c' format--it only does SQL
(which should work fine for you unless you have large objects, which I
doubt SQL-Ledger uses). Furthermore, whenever you use pg_dump or
pg_dumpall, you need to redirect it to a file:

$ pg_dumpall > /var/tmp/backup.sql

Otherwise the backup will go to your terminal and nowhere else--not
very useful. :)

I highly suggest you read:

http://www.postgresql.org/docs/8.1/static/backup.html

Since it looks like you have both old and new data directories, you
might be able to get the old server running again, dump out the data
and load it into the new server. Depends on whether your old binaries
got blown away by the upgrade.

PG is a bit tricky to upgrade and I haven't yet seen a distro upgrade
script that works really well.

-Doug

On Thu, 19 Jan 2006, Doug McNaught wrote:

> Furthermore, whenever you use pg_dump or pg_dumpall, you need to redirect
> it to a file:
>
> $ pg_dumpall > /var/tmp/backup.sql

I was just about to try this; it seemed the way to go.

> I highly suggest you read:
> http://www.postgresql.org/docs/8.1/static/backup.html

Will do. Also, just downloaded the 8.1 pdf manual(s).

> Since it looks like you have both old and new data directories, you might
> be able to get the old server running again, dump out the data and load it
> into the new server. Depends on whether your old binaries got blown away by
> the upgrade.

Yes, they are gone. I can uninstall the 8.1.2 and restore everything from
the backup tape. Perhaps I'll hear from the Slackware packager about this.

> PG is a bit tricky to upgrade and I haven't yet seen a distro upgrade
> script that works really well.

It is the "bit tricky" part that has put me off before now. I've also
absolutely no objection to building from source, either, but a pre-built
package for my distribution seemed quicker and easier. Guess where I blew it
was not redirecting the original pg_dumpall to the file.

Using the 8.1.2 pg_dumpall from the 2004 data directory produces a 819K
file. But, when I then cd to the new data directory and run:

postgres(at)salmo:/var/lib/pgsql/data$ pg_restore /var/tmp/backup.sql
pg_restore: [archiver] input file does not appear to be a valid archive

However, the file is readable and looks to have all the databases and tables
in it. Perhaps I'm closer now?

Rich

--
Richard B. Shepard, Ph.D. | Author of "Quantifying Environmental
Applied Ecosystem Services, Inc. (TM) | Impact Assessments Using Fuzzy Logic"
<http://www.appl-ecosys.com> Voice: 503-667-4517 Fax: 503-667-8863

Rich Shepard <rshepard(at)appl-ecosys(dot)com> writes:

> Using the 8.1.2 pg_dumpall from the 2004 data directory produces a 819K
> file. But, when I then cd to the new data directory and run:
>
> postgres(at)salmo:/var/lib/pgsql/data$ pg_restore /var/tmp/backup.sql
> pg_restore: [archiver] input file does not appear to be a valid archive
>
> However, the file is readable and looks to have all the databases and tables
> in it. Perhaps I'm closer now?

Yes, as I said before, 'pg_dumpall' only produces SQL format dumps,
which you restore using 'psql'. 'pg_restore' is only for binary
dumps.

-Doug

On Thu, 19 Jan 2006, Doug McNaught wrote:

> Yes, as I said before, 'pg_dumpall' only produces SQL format dumps, which
> you restore using 'psql'. 'pg_restore' is only for binary dumps.

Doug,

I read the backup/restore web page to which you pointed me. I think that
the new database is not initiated. When I follow the instructions and enter:

psql -f /var/tmp/backup.sql

I see:

psql: FATAL: database "postgres" does not exist

This is the same message I read when I append 'postgres' to the command. Back
to the manual.

Thanks,

Rich

--
Richard B. Shepard, Ph.D. | Author of "Quantifying Environmental
Applied Ecosystem Services, Inc. (TM) | Impact Assessments Using Fuzzy Logic"
<http://www.appl-ecosys.com> Voice: 503-667-4517 Fax: 503-667-8863

Rich Shepard wrote:
> On Thu, 19 Jan 2006, Doug McNaught wrote:
>
>> Yes, as I said before, 'pg_dumpall' only produces SQL format dumps, which
>> you restore using 'psql'. 'pg_restore' is only for binary dumps.
>
> Doug,
>
> I read the backup/restore web page to which you pointed me. I think that
> the new database is not initiated. When I follow the instructions and
> enter:
>
> psql -f /var/tmp/backup.sql
>
> I see:
>
> psql: FATAL: database "postgres" does not exist

What happens when you psql -U postgres template1 ?

>
> This is the same message I read when I append 'postgres' to the command.
> Back
> to the manual.
>
> Thanks,
>
> Rich
>

--
The PostgreSQL Company - Command Prompt, Inc. 1.503.667.4564
PostgreSQL Replication, Consulting, Custom Development, 24x7 support
Managed Services, Shared and Dedicated Hosting
Co-Authors: plPHP, plPerlNG - http://www.commandprompt.com/

On Thu, 19 Jan 2006, Joshua D. Drake wrote:

> What happens when you psql -U postgres template1 ?

A-ha!

Welcome to psql 8.1.2 (server 7.4.3), the PostgreSQL interactive terminal.

Type: \copyright for distribution terms
\h for help with SQL commands
\? for help with psql commands
\g or terminate with semicolon to execute query
\q to quit

WARNING: You are connected to a server with major version 7.4,
but your psql client is major version 8.1. Some backslash commands,
such as \d, might not work properly.

Now, it's accessing data in /usr/local/pgsql/data. So, it's now necessary
to stop the old server and start the new one. I thought that I had done that,
but obviously not.

Your patience, Josh and Doug, is very much appreciated. I'm learning what I
should probably have learned several years ago, and these messages will be
kept for future reference. What's the next step?

Rich

--
Richard B. Shepard, Ph.D. | Author of "Quantifying Environmental
Applied Ecosystem Services, Inc. (TM) | Impact Assessments Using Fuzzy Logic"
<http://www.appl-ecosys.com> Voice: 503-667-4517 Fax: 503-667-8863

Rich Shepard <rshepard(at)appl-ecosys(dot)com> writes:
> psql -f /var/tmp/backup.sql
> I see:
> psql: FATAL: database "postgres" does not exist

This looks like you are trying to use an 8.1 psql to talk to a 7.4
postmaster. Database "postgres" should exist by default in an 8.1
installation but it would not in 7.4. Better check which postmaster
is really running.

regards, tom lane

On Thu, 19 Jan 2006, Tom Lane wrote:

> This looks like you are trying to use an 8.1 psql to talk to a 7.4
> postmaster. Database "postgres" should exist by default in an 8.1
> installation but it would not in 7.4. Better check which postmaster is
> really running.

Tom,

Yes. That seems to have been the problem. I killed the running postmaster
(I presume 7.4.3) process and finally restarted the new one. I had permission
problems on /var/lib/pgsql/data (it needed to be 0700), then I could start it
using the script (/etc/rc.d/rc.postgresql start). Now I see a new process,
27964 pts/2 S 0:00 /usr/bin/postmaster -D /var/lib/pgsql/data
where the old one was pointing to a data directory of /usr/local/pgsql/data.

So far so good. Then, as user postgres and in /var/lib/pgsql I ran 'psql -f
/var/tmp/backup.sql' ... and the screen was filled with what looked like good
progress. I've attached the serverlog.

But, trying to access SQL-Ledger still gives me internal server errors when
I try to log in.

Rich

--
Richard B. Shepard, Ph.D. | Author of "Quantifying Environmental
Applied Ecosystem Services, Inc. (TM) | Impact Assessments Using Fuzzy Logic"
<http://www.appl-ecosys.com> Voice: 503-667-4517 Fax: 503-667-8863

On Thu, 19 Jan 2006, Rich Shepard wrote:

> I've attached the serverlog.

Oops! Let me try this again.

Rich

--
Richard B. Shepard, Ph.D. | Author of "Quantifying Environmental
Applied Ecosystem Services, Inc. (TM) | Impact Assessments Using Fuzzy Logic"
<http://www.appl-ecosys.com> Voice: 503-667-4517 Fax: 503-667-8863

On Thu, 19 Jan 2006, Joshua D. Drake wrote:

> What happens when you psql -U postgres template1 ?

Josh,

OK. Now it's all straightened out on this side:

postgres(at)salmo:/var/lib/pgsql$ psql -U postgres template1
Welcome to psql 8.1.2, the PostgreSQL interactive terminal.

Type: \copyright for distribution terms
\h for help with SQL commands
\? for help with psql commands
\g or terminate with semicolon to execute query
\q to quit

template1=# \q

No more server conflicts. Whew! It seems that I have postgres properly --
and fully -- upgraded.

However, I'm still getting a 500 server error from httpd when I try to log
in to sql-ledger (http://localhost/sql-ledger/login.pl). What do I need to do
to identify the reason?

Many thanks,

Rich

--
Richard B. Shepard, Ph.D. | Author of "Quantifying Environmental
Applied Ecosystem Services, Inc. (TM) | Impact Assessments Using Fuzzy Logic"
<http://www.appl-ecosys.com> Voice: 503-667-4517 Fax: 503-667-8863

On Thu, 19 Jan 2006, Rich Shepard wrote:

> However, I'm still getting a 500 server error from httpd when I try to log
> in to sql-ledger (http://localhost/sql-ledger/login.pl). What do I need to
> do to identify the reason?

I stopped and restarted httpd. No relief there.

Rich

--
Richard B. Shepard, Ph.D. | Author of "Quantifying Environmental
Applied Ecosystem Services, Inc. (TM) | Impact Assessments Using Fuzzy Logic"
<http://www.appl-ecosys.com> Voice: 503-667-4517 Fax: 503-667-8863

> No more server conflicts. Whew! It seems that I have postgres properly --
> and fully -- upgraded.
>
> However, I'm still getting a 500 server error from httpd when I try to
> log
> in to sql-ledger (http://localhost/sql-ledger/login.pl). What do I need
> to do
> to identify the reason?

What does your apache error log say? I am guess that DBD::Pg can no
longer find libpq and you need to recompile DBD::Pg.

Joshua D. Drake

>
> Many thanks,
>
> Rich
>

--
The PostgreSQL Company - Command Prompt, Inc. 1.503.667.4564
PostgreSQL Replication, Consulting, Custom Development, 24x7 support
Managed Services, Shared and Dedicated Hosting
Co-Authors: plPHP, plPerlNG - http://www.commandprompt.com/

On Thu, 19 Jan 2006, Joshua D. Drake wrote:

> What does your apache error log say? I am guess that DBD::Pg can no longer
> find libpq and you need to recompile DBD::Pg.

Joshua,

Yup. That seems to be the case. I'll dig in my stored messages for how to
re-install DBD::Pg.

Thanks,

Rich

--
Richard B. Shepard, Ph.D. | Author of "Quantifying Environmental
Applied Ecosystem Services, Inc. (TM) | Impact Assessments Using Fuzzy Logic"
<http://www.appl-ecosys.com> Voice: 503-667-4517 Fax: 503-667-8863

On Thu, 19 Jan 2006, Joshua D. Drake wrote:

> What does your apache error log say? I am guess that DBD::Pg can no longer
> find libpq and you need to recompile DBD::Pg.

Josh,

No, that's not quite right.

I re-installed DBD::Pg and was told that it's up to date. Then I stopped
and restarted httpd. Tailed the error.log and found:

[Thu Jan 19 17:22:05 2006] [warn] NameVirtualHost *:80 has no VirtualHosts
PHP Warning: Unknown(): Unable to load dynamic library
'/usr/lib/php/extensions/pgsql.so' - /usr/lib/php/extensions/pgsql.so: cannot
open shared object file: No such file or directory in Unknown on line 0

Sure enough. There is no pgsql.so on the system. I have php-4.4.1-i486-2
installed here; just upgraded from the -1 build. A Google search suggests
that pgsql.so should be with either php or postgres; probably the former.

Suggestions where I can get it? It's either in the Slackware package and
not being seen by the system, or something has seriously broken.

Thanks,

Rich

--
Richard B. Shepard, Ph.D. | Author of "Quantifying Environmental
Applied Ecosystem Services, Inc. (TM) | Impact Assessments Using Fuzzy Logic"
<http://www.appl-ecosys.com> Voice: 503-667-4517 Fax: 503-667-8863

> Sure enough. There is no pgsql.so on the system. I have php-4.4.1-i486-2
> installed here; just upgraded from the -1 build. A Google search suggests
> that pgsql.so should be with either php or postgres; probably the former.

If it is slackware I have no idea but on fedora it would be something
like php-pgsql.

If you are not using PHP then you can comment the requirement from your
php.ini.

Joshua D. Drake

>
> Suggestions where I can get it? It's either in the Slackware package and
> not being seen by the system, or something has seriously broken.
>
> Thanks,
>
> Rich
>

--
The PostgreSQL Company - Command Prompt, Inc. 1.503.667.4564
PostgreSQL Replication, Consulting, Custom Development, 24x7 support
Managed Services, Shared and Dedicated Hosting
Co-Authors: plPHP, plPerlNG - http://www.commandprompt.com/

On Thu, 19 Jan 2006, Joshua D. Drake wrote:

> If it is slackware I have no idea but on fedora it would be something like
> php-pgsql.

Josh,

I've searched the Slackware package repository and there is no php-pgsql.
Patrick puts mysql.so in php, but not pgsql.so.

> If you are not using PHP then you can comment the requirement from your
> php.ini.

I don't think that SQL-Ledger uses it, but that's the error when I try to
log in. Other web-based apps (webcollab and -- if I can get it running --
xrms) do require php.

Thanks,

Rich

--
Richard B. Shepard, Ph.D. | Author of "Quantifying Environmental
Applied Ecosystem Services, Inc. (TM) | Impact Assessments Using Fuzzy Logic"
<http://www.appl-ecosys.com> Voice: 503-667-4517 Fax: 503-667-8863

Disable php on Apache if you dont use it....LoadModule php4_module....

Rich Shepard wrote:

> On Thu, 19 Jan 2006, Joshua D. Drake wrote:
>
>> If it is slackware I have no idea but on fedora it would be something
>> like php-pgsql.
>
>
> Josh,
>
> I've searched the Slackware package repository and there is no
> php-pgsql.
> Patrick puts mysql.so in php, but not pgsql.so.
>
>> If you are not using PHP then you can comment the requirement from your
>> php.ini.
>
>
> I don't think that SQL-Ledger uses it, but that's the error when I
> try to
> log in. Other web-based apps (webcollab and -- if I can get it running --
> xrms) do require php.
>
> Thanks,
>
> Rich
>

On Thu, 19 Jan 2006, Rodrigo Gonzalez wrote:

> Disable php on Apache if you dont use it....LoadModule php4_module....

There are a couple of apps that require it, even though I don't use them
often.

Anywho, I'll just rebuild php --with-pgsql and make my own Slackware
package with it.

Thanks all,

Rich

--
Richard B. Shepard, Ph.D. | Author of "Quantifying Environmental
Applied Ecosystem Services, Inc. (TM) | Impact Assessments Using Fuzzy Logic"
<http://www.appl-ecosys.com> Voice: 503-667-4517 Fax: 503-667-8863