Postgresql 8.3 not responding

[Murray Cumming]

In Ubuntu Hardy Heron, Postgresql 8.3 does not seem to be working at all. For instance:

murrayc@murrayc-desktop:~$ sudo /etc/init.d/postgresql-8.3 stop
 * Stopping PostgreSQL 8.3 database server [ OK ]
murrayc@murrayc-desktop:~$ ps -ef | grep "postgres"
murrayc 19335 18965 0 16:08 pts/5 00:00:00 grep postgres
murrayc@murrayc-desktop:~$ sudo /etc/init.d/postgresql-8.3 start
 * Starting PostgreSQL 8.3 database server [ OK ]
murrayc@murrayc-desktop:~$ ps -ef | grep "postgres"
postgres 19357 1 22 16:08 ? 00:00:01 /usr/lib/postgresql/8.3/bin/postgres -D /var/lib/postgresql/8.3/main -c config_file=/etc/postgresql/8.3/main/postgresql.conf
postgres 19359 19357 0 16:08 ? 00:00:00 postgres: writer process
postgres 19360 19357 0 16:08 ? 00:00:00 postgres: wal writer process
postgres 19361 19357 0 16:08 ? 00:00:00 postgres: autovacuum launcher process
postgres 19362 19357 0 16:08 ? 00:00:00 postgres: stats collector process
murrayc 19377 18965 0 16:08 pts/5 00:00:00 grep postgres
murrayc@murrayc-desktop:~$ psql
psql: could not connect to server: No such file or directory
 Is the server running locally and accepting
 connections on Unix domain socket "/var/run/postgresql/.s.PGSQL.5432"?

[Murray Cumming (murrayc)]

Maybe this last line from /var/log/postgresql/postgresql-8.3-main.log is relevant:

2008-02-20 16:08:14 CET LOG: database system is ready to accept connections
2008-02-20 16:08:14 CET LOG: incomplete startup packet

[Murray Cumming (murrayc)]

This seems very serious, but I wonder if anyone else is experiencing this.

[Mathias Gug (mathiaz)]

Confirmed on a fresh new install from ubuntu-server-amd64 iso (alpha4 and alpha5).

[Mathias Gug (mathiaz)]

The post installation fails because pg_createcluster exits with an error during the installation:
 Error: The locale requested by the environment is invalid.

Langage packs are not installed by default on ubuntu-server - thus the failure.

[Mathias Gug (mathiaz)]

After further investagation, I've found the following error messages in the syslog from an ubuntu-server alpha6 install:

Mar 11 03:19:59 in-target: SETTING UP POSTGRESQL-8.3 (8.3.0-1) ...^M
Mar 11 03:19:59 in-target: CREATING NEW CLUSTER (CONFIGURATION: /ETC/POSTGRESQL/8.3/MAIN, DATA: /VAR/LIB/POSTGRESQL/8.3/MAIN)...^M
Mar 11 03:19:59 in-target: SH: CANNOT CREATE /DEV/NULL: PERMISSION DENIED^M
Mar 11 03:19:59 in-target: FGETS FAILURE: SUCCESS^M
Mar 11 03:19:59 in-target: THE PROGRAM "POSTGRES" IS NEEDED BY INITDB BUT WAS NOT FOUND IN THE^M
Mar 11 03:19:59 in-target: SAME DIRECTORY AS "/USR/LIB/POSTGRESQL/8.3/BIN/INITDB".^M
Mar 11 03:19:59 in-target: CHECK YOUR INSTALLATION.^M
Mar 11 03:19:59 in-target: ERROR:
Mar 11 03:19:59 in-target: INITDB FAILED
Mar 11 03:19:59 in-target: ^M
Mar 11 03:19:59 in-target: ERROR: COULD NOT CREATE DEFAULT CLUSTER. PLEASE CREATE IT MANUALLY WITH^M
Mar 11 03:19:59 in-target: ^M
Mar 11 03:19:59 in-target: PG_CREATECLUSTER 8.3 MAIN --START^M
Mar 11 03:19:59 in-target: ^M
Mar 11 03:19:59 in-target: OR A SIMILAR COMMAND (SEE 'MAN PG_CREATECLUSTER').^M

I've attached the whole syslog file.

[Martin Pitt (pitti)]

Hm, the installation environment seems seriously broken then -- no /dev/null?

Murray, what does "pg_lsclusters" show for you? It will display the port and status of the cluster.

Also, what does

  sudo /usr/lib/postgresql/8.3/bin/pg_controldata /var/lib/postgresql/8.3/main

show? That'll reveal the encodings.

Mathias, are you sure about the missing locales? You should never set $LANG to a locale that does not exist. This is a serious bug that will wreak havoc in many different places, not even mentioning stderr output for every setlocale() call any program does.

So, is there anything to fix in postgresql-8.3 itself, or should this be reassigned to the installer?

[Murray Cumming (murrayc)]

> Murray, what does "pg_lsclusters" show for you? It will display the port and status of the cluster.

murrayc@murrayc-desktop:~$ pg_lsclusters
Version Cluster Port Status Owner Data directory Log file
8.3 main 5433 online postgres /var/lib/postgresql/8.3/main /var/log/postgresql/postgresql-8.3-main.log
murrayc@murrayc-desktop:~$

> Also, what does
> sudo /usr/lib/postgresql/8.3/bin/pg_controldata /var/lib/postgresql/8.3/main
> show? That'll reveal the encodings.

murrayc@murrayc-desktop:~$ sudo /usr/lib/postgresql/8.3/bin/pg_controldata /var/lib/postgresql/8.3/main
[sudo] password for murrayc:
pg_control version number: 833
Catalog version number: 200711281
Database system identifier: 5163503598208647034
Database cluster state: in production
pg_control last modified: Tue 11 Mar 2008 11:01:19 AM CET
Latest checkpoint location: 0/44051C
Prior checkpoint location: 0/4404DC
Latest checkpoint's REDO location: 0/44051C
Latest checkpoint's TimeLineID: 1
Latest checkpoint's NextXID: 0/387
Latest checkpoint's NextOID: 11512
Latest checkpoint's NextMultiXactId: 1
Latest checkpoint's NextMultiOffset: 0
Time of latest checkpoint: Mon 10 Mar 2008 10:44:55 PM CET
Minimum recovery ending location: 0/0
Maximum data alignment: 4
Database block size: 8192
Blocks per segment of large relation: 131072
WAL block size: 8192
Bytes per WAL segment: 16777216
Maximum length of identifiers: 64
Maximum columns in an index: 32
Maximum size of a TOAST chunk: 2000
Date/time type storage: 64-bit integers
Maximum length of locale name: 128
LC_COLLATE: en_US.UTF-8
LC_CTYPE: en_US.UTF-8
murrayc@murrayc-desktop:~$

[Martin Pitt (pitti)]

Murray Cumming [2008-03-11 12:25 -0000]:
> > Murray, what does "pg_lsclusters" show for you? It will display the
> port and status of the cluster.
>
> murrayc@murrayc-desktop:~$ pg_lsclusters
> Version Cluster Port Status Owner Data directory Log file
> 8.3 main 5433 online postgres /var/lib/postgresql/8.3/main /var/log/postgresql/postgresql-8.3-main.log

Ah, it listens on port 5433. Apparently there already was a cluster on
the standard 5432 port when this one was created?

> murrayc@murrayc-desktop:~$ sudo /usr/lib/postgresql/8.3/bin/pg_controldata /var/lib/postgresql/8.3/main
> [...]
> LC_COLLATE: en_US.UTF-8
> LC_CTYPE: en_US.UTF-8

So that locale must exist, since that is verified on both cluster
creation and startup.

Thanks,

Martin

[Murray Cumming (murrayc)]

> Ah, it listens on port 5433. Apparently there already was a cluster on
> the standard 5432 port when this one was created?

It's possible, but I don't think so. postgres 8.2 was installed, and I think that also listened on 5433 by default. I think it was just a standard Gutsy install with postgres. Sorry for not being able to give precise clues.

[Mathias Gug (mathiaz)]

Hi martin,

I remove my comments about the missing locales. So I think there isn't anything specific to postgresql-8.3 for now.

It seems that this is a problem in the installer.

[Martin Pitt (pitti)]

Closing postgresql task then. Please reopen and let me know if there's something to be fixed there.

[Murray Cumming (murrayc)]

Hang on, My PostgreSQL 8.3 installation was broken after doing a simple apt-get install of it (well, with synaptic actually), and I haven't yet seen either
a) A way to fix it.
b) A way to stop the install from being broken in future either.

I did not install Ubuntu Hardy from scratch so I don't see what debian-installer has to do with it.

[Colin Watson (cjwatson)]

I don't understand how this is a d-i bug either.

Murray, how *did* you install Ubuntu Hardy? Further, what does 'ls -l /dev/null' say?

[Murray Cumming (murrayc)]

> Murray, how *did* you install Ubuntu Hardy?

I think it was an install from a Ubuntu Gutsy CD after Gutsy was released as stable. Then I recently did "upgrade-manager -d" to upgrade to hardy.

> Further, what does 'ls -l /dev/null' say?

murrayc@murrayc-laptop:~$ ls -l /dev/null
crw-rw-rw- 1 root root 1, 3 2008-03-02 22:22 /dev/null

[Colin Watson (cjwatson)]

Hmm. I think Mathias' bug is indeed entirely separate, then.

[Colin Watson (cjwatson)]

Reopening postgresql task, even if we don't understand it yet; I'll reject the d-i task.

[Martin Pitt (pitti)]

So, admittedly I don't have the slightest idea about the /dev/null issue. But that was actually only in a log from Matthias. From the bug trail I assume that you actually didn't have that problem?

So, as far as I can see the remaining issue is that the 8.3 cluster was created on port 5433, but 'psql' does not default to using this port (which it should).

 * Can you please confirm that "psql -l -p 5433" works?

 * Do you have a ~/.postgresqlrc, and does /etc/postgresql-common/user_clusters contains anything except comments?

 * Does "readlink /usr/bin/psql" print "../share/postgresql-common/pg_wrapper"?

Thank you!

[Murray Cumming (murrayc)]

The problem now seems to have resolved itself. I guess it was fixed by some update that I did in the meantime. Specifically, psql now complains correctly about failed authorization, instead of complaining about not being able to connect to the server. And after doing a "createuser murrayc" as the postsgres user, I can login normally to psql.

Thanks for the attention.

[Colin Watson (cjwatson)]

Just to update on Mathias' issue, we know what that was now: udev-udeb doesn't contain 40-permissions.rules, and can't because that specifies a number of groups that don't exist in d-i. Scott is working on splitting this out so that the part of that rules file that sets the permissions on /dev/null can be included in d-i.

[kanenk (kanenk)]

Good evening,

I have some problems with postgresql-8.3 too. A fresh install of ubuntu 8.04 server Beta1 from zero. I followed the installation process and checked postgresql during the installation. All seemed to be okay.
Installation finished, postgresql does not appear in a 'ps -edf' list. Nothing appears in the /etc/postgres/ and /var/log/postgres/ folders.
The user postgres and group postgres exists in /etc/passwd. When I launch /etc/init.d/postgresql-8.3, nothing happens.
I have the same problem even after a "dpkg-reconfigure postgresql" or after downloaded the package from ubuntu site and a "dpkg -i postgresql_8.3.1-1_all.deb".
I have :

psql -l -p 5432
psql: could not connect to server: No such file or directory
        Is the server running locally and accepting
        connections on Unix domain socket "/var/run/postgresql/.s.PGSQL.5432"?

I have no ~/.postgresqlrc file

The file /etc/postgresql-common/user_clusters is all commented

And : "readlink /usr/bin/psql" prints
../share/postgresql-common/pg_wrapper

me@server:~$ sudo /etc/init.d/postgresql-8.3 stop
me@server:~$ ps -edf | grep postgres
me 30935 30782 0 01:24 pts/0 00:00:00 grep postgres
me@server:~$ sudo /etc/init.d/postgresql-8.3 start
me@server:~$ ps -edf | grep postgres
me 30939 30782 0 01:24 pts/0 00:00:00 grep postgres
me@server:~$ psql
psql: could not connect to server: No such file or directory
        Is the server running locally and accepting
        connections on Unix domain socket "/var/run/postgresql/.s.PGSQL.5432"?

Thank you for the help.

[Martin Pitt (pitti)]

kanenk, seems that the installation did not create a cluster at all. What happens if you do

  sudo pg_createcluster 8.3 main --start

? Does it work afterwards? If so, then something at install time went wrong. Do you still have some logs in /var/log/installer which point out the problem?

[kanenk (kanenk)]

Hi Martin,

Everything goes alright now with "sudo pg_createcluster 8.3 main --start".
I've seen other people that the same problem as me (http://forum.ubuntu-fr.org/viewtopic.php?id=197108)
Seemed to be a problem during installation : (extract from syslog)

Mar 24 04:27:30 in-target: Paramétrage de postgresql-client-common (86) ...
Mar 24 04:27:30 in-target: Paramétrage de postgresql-client-8.3 (8.3.0-1) ...
Mar 24 04:27:31 in-target:
Mar 24 04:27:31 in-target: Paramétrage de postgresql-common (86) ...
Mar 24 04:27:31 debconf: Obsolete command TITLE Configuration de postgresql-common called
Mar 24 05:27:31 groupadd[7337]: new group: name=postgres, GID=115
Mar 24 05:27:31 useradd[7338]: new user: name=postgres, UID=107, GID=115, home=/var/lib/postgresql, shell=/bin/bash
Mar 24 05:27:31 usermod[7339]: change user `postgres' password
Mar 24 05:27:31 chage[7340]: changed password expiry for postgres
Mar 24 05:27:31 chfn[7341]: changed user `postgres' information
Mar 24 04:27:31 in-target: Adding user postgres to group ssl-cert
Mar 24 05:27:31 gpasswd[7353]: add member postgres to group ssl-cert by root
Mar 24 04:27:31 in-target:
Mar 24 04:27:31 in-target: Paramétrage de postgresql-8.3 (8.3.0-1) ...
Mar 24 04:27:31 in-target: Creating new cluster (configuration: /etc/postgresql/8.3/main, data: /var/lib/postgresql/8.3/main)...
Mar 24 04:27:31 in-target: sh: cannot create /dev/null: Permission denied
Mar 24 04:27:31 in-target: fgets failure: Success
Mar 24 04:27:31 in-target: The program "postgres" is needed by initdb but was not found in the
Mar 24 04:27:31 in-target: same directory as "/usr/lib/postgresql/8.3/bin/initdb".
Mar 24 04:27:31 in-target: Check your installation.
Mar 24 04:27:31 in-target: Error:
Mar 24 04:27:31 in-target: initdb failed
Mar 24 04:27:31 in-target:
Mar 24 04:27:31 in-target: Error: could not create default cluster. Please create it manually with
Mar 24 04:27:31 in-target:
Mar 24 04:27:31 in-target: pg_createcluster 8.3 main --start
Mar 24 04:27:31 in-target:
Mar 24 04:27:31 in-target: or a similar command (see 'man pg_createcluster').
Mar 24 04:27:32 in-target:
Mar 24 04:27:32 in-target: Paramétrage de postgresql (8.3.0-1) ...
Mar 24 04:27:32 in-target: Paramétrage de postgresql-client (8.3.0-1) ...
Mar 24 04:27:32 in-target: Paramétrage de postgresql-contrib-8.3 (8.3.0-1) ...
Mar 24 04:27:32 in-target:
Mar 24 04:27:32 in-target: Paramétrage de postgresql-contrib (8.3.0-1) ...
Mar 24 04:27:32 in-target: Paramétrage de postgresql-doc-8.3 (8.3.0-1) ...
Mar 24 04:27:32 in-target:
Mar 24 04:27:32 in-target: Paramétrage de postgresql-doc (8.3.0-1) ...

This error is somehow strange, I installed the server on formatted partitions except the /home partition.

Thank you very much for your help.
Kane.

----------------------------------------
> From: <email address hidden>
> To: <email address hidden>
> Date: Mon, 31 Mar 2008 17:17:15 +0000
> Subject: [Bug 193696] Re: Postgresql 8.3 not responding
>
> kanenk, seems that the installation did not create a cluster at all.
> What happens if you do
>
> sudo pg_createcluster 8.3 main --start
>
> ? Does it work afterwards? If so, then something at install time went
> wrong. ...

[Martin Pitt (pitti)]

Hi kanenk,

kanenk [2008-03-31 20:40 -0000]:
> Mar 24 04:27:31 in-target: sh: cannot create /dev/null: Permission denied

Here we are again, that strange installer bug. Why was that marked as
"invalid"?

[Steve Langasek (vorlon)]

On Tue, Apr 01, 2008 at 06:46:57AM -0000, Martin Pitt wrote:
> Hi kanenk,

> kanenk [2008-03-31 20:40 -0000]:
> > Mar 24 04:27:31 in-target: sh: cannot create /dev/null: Permission denied

> Here we are again, that strange installer bug. Why was that marked as
> "invalid"?

Because it ended up under bug #204108, I think.

[Colin Watson (cjwatson)]

Correct. That issue is *not* the same as the one filed in this bug.

[Neal McBurnett (nealmcb)]

I just did a fresh hardy install into a kvm vm via ubuntu-vm-builder on hardy, and installed ebox in the vm, and ran into the same symtoms that kanenk did. And the tip to try

 sudo pg_createcluster 8.3 main --start

worked like a charm....

But why did this happen on another very fresh install?

[Mathias Gug (mathiaz)]

On Wed, Apr 23, 2008 at 03:52:22AM -0000, Neal McBurnett wrote:
> I just did a fresh hardy install into a kvm vm via ubuntu-vm-builder on

There may be some issues with installing postgresql within
ubuntu-vm-builder. I'd open a new bug against ubuntu-vm-builder.

--
Mathias Gug
Ubuntu Developer http://www.ubuntu.com

[Neal McBurnett (nealmcb)]

The install was actually done with apt-get. But you're right, Mathias - the problem was a result of ubuntu-vm-builder....

I ran it again and noticed what the real problem was. By default ubuntu-vm-builder doesn't set up a locale other than "C". When I log in via ssh, LANG=en_US.UTF-8 is picked up from my environment. postgresql doesn't like that outage:

Setting up postgresql-8.3 (8.3.1-1) ...
perl: warning: Setting locale failed.
perl: warning: Please check that your locale settings:
 LANGUAGE = (unset),
 LC_ALL = (unset),
 LANG = "en_US.UTF-8"
    are supported and installed on your system.
perl: warning: Falling back to the standard locale ("C").
Error: The locale requested by the environment is invalid.
Error: could not create default cluster. Please create it manually with

  pg_createcluster 8.3 main --start

I'll have to get in the habit of saving the output of big installs like this so I can dig out those error messages. (Or maybe apt-get could do that by default?)
And I'll think about how ubuntu-vm-builder can be improved so this doesn't happen....

[Neal McBurnett (nealmcb)]

I reported my underlying problem as
 ubuntu-vm-builder should set up a locale, timezone, etc
 https://bugs.edge.launchpad.net/ubuntu/+source/ubuntu-vm-builder/+bug/221231

[Laurent Dinclaux (dreadlox)]

Bug exists on intrepid if no locales installed.

If you install postresql package before locales, you'll be in trouble. Even removing postgresql an reinstalling it after installing locales package won't work: sould issue "sudo pg_createcluster 8.3 main --start"

There should be a warning telling no locales installed and after installing locales postgresql should run with issuing any further command!

[Martin Pitt (pitti)]

Hm, postgresql-8.3 has a strict dependency on "locales", so that should work..

[Laurent Dinclaux (dreadlox)]

I can reproduce it on any new Intrepid server install

[Robrass (robrassler)]

I seem to be on the same bug, I can see it is in discussion but not what to do about it.

Downloaded ubuntu server 23 June 09
Installed today and selected postgresql as part of the install.

I couldn't get it to start up -

 ./postgresql-8.3 start
 * Starting PostgreSQL 8.3 database server
 * Error: Could not create log file /var/log/postgresql/postgresql-8.3-main.log

I have tried all sorts of chown and rights on the directory and file, nothing works.

I loaded up webmin and it can't seem to make head-nor-tail of this postgresql install either.

createdb example - seemed to work, but as I can not get into pgsql I can't actually confirm it.

My main reason for loading ubuntu server into a VM was as a database server (and a few other bits), but I can't see any actual reason why postgresql will not run.

Those who have got it running, what did you actually do?

[Martin Pitt (pitti)]

Robrass, this looks like an entirely different problem. Does /var/log/postgresql/ exist? What's in it?

  ls -ld /var/log/postgresql/
  ls -l /var/log/postgresql/