PostgreSQL Problems After Upgrade

Carlwill · 2010-11-01 14:25:35

I did a search but couldn't find anyone else having this problem. I did a upgrade from 8.4 > 9.0 of PostgreSQL and for some reason Arch Linux shows the daemon starting fine with no errors however I am unable to connect to it at all:

[cmennens@slave ~]$ sudo /etc/rc.d/postgresql restart
Password: 
:: Stopping PostgreSQL                                                                                                                                               [BUSY] pg_ctl: PID file "/var/lib/postgres/data/postmaster.pid" does not exist
Is server running?
                                                                                                                                                                     [FAIL] 
:: Starting PostgreSQL                                                                                                                                               [BUSY] server starting
                                                                                                                                                                     [DONE] 
[cmennens@slave ~]$ psql -l
psql: could not connect to server: No such file or directory
    Is the server running locally and accepting
    connections on Unix domain socket "/tmp/.s.PGSQL.5432"?

It shows that it starts fine with no errors from the command above however when I attempt to connect to it, it can't find the daemon running or something to that nature. Any ideas what is wrong? I've checked and I am running the latest version available in Pacman and I've rebooted twice.

[cmennens@slave ~]$ uname -r
2.6.35-ARCH
[cmennens@slave ~]$ pacman -Qs postgresql
local/postgresql 9.0.1-2
    A sophisticated object-relational DBMS
local/postgresql-libs 9.0.1-2
    Libraries for use with PostgreSQL

dedanna1029 · 2010-11-01 14:51:45

I'm having the exact same issues, and can confirm this - it's hosing my music db...

http://bjoernvold.com/forum/viewtopic.php?f=20&t=822

Carlwill · 2010-11-01 14:55:16

My entire database at work is down. Luckily I was able to pull my nightly SQL dump and pump it in my Debian server running PostgreSQL but I still would love to understand what's the deal. Should I file a 'bug'?

dedanna1029 · 2010-11-01 14:56:13

I think so. I'll be right behind you and will comment and vote for it, if you post the link here.

Last edited by dedanna1029 (2010-11-01 14:56:33)

Carlwill · 2010-11-01 15:07:51

https://bugs.archlinux.org/task/21560

dedanna1029 · 2010-11-01 15:16:09

Done, and thank you.

Last edited by dedanna1029 (2010-11-01 15:16:21)

coat · 2010-11-01 16:18:53

Did you check /var/log/postgresql.log?

Here's mine:
FATAL: database files are incompatible with server
DETAIL: The data directory was initialized by PostgreSQL version 8.4, which is not compatible with this version 9.0.1.

You will want to run pg_upgrade, probably moving /var/lib/postgresql/data to another directory before you do since for some reason arch doesn't keep it in a separate versioned directory, e.g. /var/lib/postgresql/8.4/data

Carlwill · 2010-11-01 16:33:39

Yup! Same here:

[root@slave ~]# tail -n 50 /var/log/postgresql.log
FATAL:  database files are incompatible with server
DETAIL:  The data directory was initialized by PostgreSQL version 8.4, which is not compatible with this version 9.0.1.
FATAL:  database files are incompatible with server
DETAIL:  The data directory was initialized by PostgreSQL version 8.4, which is not compatible with this version 9.0.1.
FATAL:  database files are incompatible with server
DETAIL:  The data directory was initialized by PostgreSQL version 8.4, which is not compatible with this version 9.0.1.

Is this a incompatibility is PostgreSQL or the way Arch packaged the upgrade? I would think PostgreSQL designed their data to be compatible between upgrades...

Last edited by Carlwill (2010-11-01 16:34:33)

skunktrader · 2010-11-01 16:36:04

https://bbs.archlinux.org/viewtopic.php?id=107400

toofishes wrote:

If you upgraded postgresql without doing a dump and restore, good luck to you. Know how your packages work.

Carlwill · 2010-11-01 16:43:39

Yup. This isn't an Arch Linux bug. I will update the ticket.

coat · 2010-11-01 16:44:44

Not that anyone should be using arch in a production server, but other distributions handle postgresql upgrades much more gracefully and don't require 'dump and restore.' That being said, always backup your database, and especially before upgrading it

Carlwill · 2010-11-01 17:10:53

coat wrote:

Not that anyone should be using arch in a production server, but other distributions handle postgresql upgrades much more gracefully and don't require 'dump and restore.' That being said, always backup your database, and especially before upgrading it :)

Nope. Regardless what distribution you're using...this is the default behavior for all major upgrades. You MUST always dump / restore you database's manually when going up major upgrades. This is the same for CentOS, Debian, Arch, etc etc etc...

http://www.postgresql.org/docs/9.0/inte … grade.html

E.2.2. Migration to Version 9.0
A dump/restore using pg_dump, or use of pg_upgrade, is required for those wishing to migrate data from any previous release.
Version 9.0 contains a number of changes that selectively break backwards compatibility in order to support new features and code quality improvements. In particular, users who make extensive use of PL/pgSQL, Point-In-Time Recovery (PITR), or Warm Standby should test their applications because of slight user-visible changes in those areas. Observe the following incompatibilities:

That's straight from the PostgreSQL docs.

coat · 2010-11-01 17:34:18

Oh I didn't realize that's changed now in 9.0. Didn't use to be the case from like 8.3 -> to 8.4 for example.

coat · 2010-11-01 17:40:53

Actually, the documentation says 'A dump/restore using pg_dump, *or* use of pg_upgrade' So we are both right

dedanna1029 · 2010-11-01 19:34:04

Okay, so what do you suggest for those who have already dumped their db (mine is simple enough to rebuild normally), when they can't even get it to start (mine dumped when I tried uninstalling/reinstalling postgresql in an effort to fix this). All I need is for postgresql to start in the first place, then I can take it from there db-wise.

Thanks.

Last edited by dedanna1029 (2010-11-01 19:34:45)

Carlwill · 2010-11-01 19:38:47

I was told that you need to move the /var/lib/postgres/data/ directory somewhere and try and restart the daemon. I took slightly more ghetto approach and remove the package completely and reinstalled after I my databases backed up. I had to set up my users and databases from scratch but I had all my data again once I imported it using:

cratedb -T template0 dbname

psql dbname < backup.sql

Last edited by Carlwill (2010-11-01 19:39:13)

dedanna1029 · 2010-11-01 19:49:41

I've just successfully downgraded:

# pacman -U /var/cache/pacman/pkg/postgresql-8.4.4-6-i686.pkg.tar.xz
resolving dependencies...
looking for inter-conflicts...

Targets (1): postgresql-8.4.4-6 [3.78 MB]

Total Download Size:    0.00 MB
Total Installed Size:   21.31 MB

Proceed with installation? [Y/n] y
checking package integrity...
(1/1) checking for file conflicts                  [######################] 100%
(1/1) upgrading postgresql                         [######################] 100%

Am going to try to get the db going again with that - if I do, then I'm going to stick with this version.

Edit: Ha, it works, and even has my db still in there LOL.

$ createdb -O amarok amarokcollection
createdb: database creation failed: ERROR:  database "amarokcollection" already exists

Very very cool.

Last edited by dedanna1029 (2010-11-01 19:52:01)

Carlwill · 2010-11-02 13:27:19

Yeah your data should still be there. Does anyone know where the hell the 'pg_upgrade' script is? I searched and can't find it on my Arch Linux system?

coat · 2010-11-02 18:05:50

Hmm, it's /usr/bin/pg_upgrade for me.

Last edited by coat (2010-11-02 18:06:01)

Carlwill · 2010-11-02 18:08:23

Yup sorry. Forgot to look in the same directory as 'pg_dump'. Duh

Arch Linux

#1 2010-11-01 14:25:35

PostgreSQL Problems After Upgrade

#2 2010-11-01 14:51:45

Re: PostgreSQL Problems After Upgrade

#3 2010-11-01 14:55:16

Re: PostgreSQL Problems After Upgrade

#4 2010-11-01 14:56:13

Re: PostgreSQL Problems After Upgrade

#5 2010-11-01 15:07:51

Re: PostgreSQL Problems After Upgrade

#6 2010-11-01 15:16:09

Re: PostgreSQL Problems After Upgrade

#7 2010-11-01 16:18:53

Re: PostgreSQL Problems After Upgrade

#8 2010-11-01 16:33:39

Re: PostgreSQL Problems After Upgrade

#9 2010-11-01 16:36:04

Re: PostgreSQL Problems After Upgrade

#10 2010-11-01 16:43:39

Re: PostgreSQL Problems After Upgrade

#11 2010-11-01 16:44:44

Re: PostgreSQL Problems After Upgrade

#12 2010-11-01 17:10:53

Re: PostgreSQL Problems After Upgrade

#13 2010-11-01 17:34:18

Re: PostgreSQL Problems After Upgrade

#14 2010-11-01 17:40:53

Re: PostgreSQL Problems After Upgrade

#15 2010-11-01 19:34:04

Re: PostgreSQL Problems After Upgrade

#16 2010-11-01 19:38:47

Re: PostgreSQL Problems After Upgrade

#17 2010-11-01 19:49:41

Re: PostgreSQL Problems After Upgrade

#18 2010-11-02 13:27:19

Re: PostgreSQL Problems After Upgrade

#19 2010-11-02 18:05:50

Re: PostgreSQL Problems After Upgrade

#20 2010-11-02 18:08:23

Re: PostgreSQL Problems After Upgrade

Board footer