Thread: 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@appl-ecosys.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@appl-ecosys.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@appl-ecosys.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@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@appl-ecosys.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@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@appl-ecosys.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
Attachment
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@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