template1 database does not exist

[Murray Cumming]

When creating the first user, on a new installation (hoary, updated to breezy),
I get an error saying that the default database, template1, does not exist:

murrayc@ubuntumurrayc:~/cvs/gnome212/glom/docs$ sudo su postgres
Password:
murrayc@ubuntumurrayc:~/cvs/gnome212/glom/docs$ createuser -P
Enter name of user to add: murrayc
Enter password for new user:
Enter it again:
Shall the new user be allowed to create databases? (y/n) y
Shall the new user be allowed to create more new users? (y/n) y
createuser: could not connect to database template1: FATAL: user "murrayc" does
not exist

I'm fairly sure that this is all I had to do in hoary to get started with postgres.

[Murray Cumming (murrayc)]

By the way, this is postgresql-7.4. I really want postgres 8 but I'm not brave
enough to try it yet.

[Martin Pitt (pitti)]

(In reply to comment #0)
> When creating the first user, on a new installation (hoary, updated to breezy),
> I get an error saying that the default database, template1, does not exist:

Well, it says that it can't connect to that database.

Can you please send the output of

pg_lsclusters
sudo -u postgres psql -l
sudo -u postgres psql template1 -c '\du'

[Martin Pitt (pitti)]

(In reply to comment #1)
> By the way, this is postgresql-7.4. I really want postgres 8 but I'm not brave
> enough to try it yet.

Oh, once your 7.4 cluster works fine, you should be able to upgrade it
seamlessly by installing "postgresql-8.0" and doing "sudo pg_upgradecluster 7.4
main".

[Murray Cumming (murrayc)]

Yes, it seems to be something more fundamental:

murrayc@ubuntumurrayc:~$ pg_lsclusters
Version Cluster Port Status Owner Data directory Log file
7.4 main 5432 down postgres /var/lib/postgresql/7.4/main
/var/log/postgresql/postgresql-7.4-main.log
murrayc@ubuntumurrayc:~$
murrayc@ubuntumurrayc:~$ sudo -u postgres psql -l
Password:
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"?
murrayc@ubuntumurrayc:~$
murrayc@ubuntumurrayc:~$ sudo -u postgres psql template1 -c '\du'
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"?
murrayc@ubuntumurrayc:~$
murrayc@ubuntumurrayc:~$ /etc/init.d/postgresql-7.4 restart
 * Restarting PostgreSQL 7.4 database server: main
 *ror: Could not change user id [fail]
murrayc@ubuntumurrayc:~$

[Martin Pitt (pitti)]

(In reply to comment #4)
> Yes, it seems to be something more fundamental:
>
> murrayc@ubuntumurrayc:~$ pg_lsclusters
> Version Cluster Port Status Owner Data directory Log file
> 7.4 main 5432 down postgres /var/lib/postgresql/7.4/main
> /var/log/postgresql/postgresql-7.4-main.log

Ok, that means that the cluster is not running, thus you can't connect to it.

> murrayc@ubuntumurrayc:~$ /etc/init.d/postgresql-7.4 restart
> * Restarting PostgreSQL 7.4 database server: main
> *ror: Could not change user id [fail]

That doesn't work as normal user, you have to do that as root. I. e. do

  sudo /etc/init.d/postgresql-7.4 start

Since this is usually done automatically, I expect that to fail (unless you
manually stopped it). If it fails, then the command should print out the last
log file snippet, can you please copy that here, too?

[Murray Cumming (murrayc)]

I'm not sure it's that simple. pg_lsclusters says it's down, but it does appear
to be running:

murrayc@ubuntumurrayc:~$ pg_lsclusters
Version Cluster Port Status Owner Data directory Log fi le
7.4 main 5432 down postgres /var/lib/postgresql/7.4/main /var/l
og/postgresql/postgresql-7.4-main.log
murrayc@ubuntumurrayc:~$
murrayc@ubuntumurrayc:~$ sudo /etc/init.d/postgresql-7.4 start
 * Starting PostgreSQL 7.4 database server: main
pg_ctl: Another postmaster may be running. Trying to start postmaster anyway.
pg_ctl: cannot start postmaster
Examine the log output.
The PostgreSQL server failed to start. Please check the log output:
FATAL: lock file "/var/lib/postgresql/7.4/main/postmaster.pid" already exists
 *NT: Is another postmaster (PID 8966) running in data directory
"/var/lib/postgresql/7.4/main"? [fail]
murrayc@ubuntumurrayc:~$
murrayc@ubuntumurrayc:~$ ps -ef | grep "postgres"
postgres 8966 1 0 11:33 ? 00:00:00
/usr/lib/postgresql/7.4/bin/postmaster -D /var/lib/postgresql/7.4/main
postgres 8969 8966 0 11:33 ? 00:00:00 postgres: stats buffer process
postgres 8970 8969 0 11:33 ? 00:00:00 postgres: stats collector process
murrayc 17011 16858 0 12:00 pts/0 00:00:00 grep postgres

[Martin Pitt (pitti)]

(In reply to comment #6)
> I'm not sure it's that simple. pg_lsclusters says it's down, but it does appear
> to be running:
>
> murrayc@ubuntumurrayc:~$ pg_lsclusters
> Version Cluster Port Status Owner Data directory Log
fi le
> 7.4 main 5432 down postgres /var/lib/postgresql/7.4/main /var/l
> FATAL: lock file "/var/lib/postgresql/7.4/main/postmaster.pid" already exists
> postgres 8966 1 0 11:33 ? 00:00:00
/usr/lib/postgresql/7.4/bin/postmaster -D /var/lib/postgresql/7.4/main

Alright, then let's dig a bit deeper. What does

  ls -la /var/run/postgresql /tmp/.s.*

say? /var/run/postgresql should contain a socket .s.PGSQL.5432, /tmp shouldn't
(unless you have a cluster not owned by postgres).

Please also do

  sudo head -n 1 /var/lib/postgresql/7.4/main/postmaster.pid

and compare the PID with

  ps aux|grep postmaster

[Murray Cumming (murrayc)]

murrayc@ubuntumurrayc:~$ ls -la /var/run/postgresql /tmp/.s.*
ls: /tmp/.s.*: No such file or directory
/var/run/postgresql:
total 12
drwxrwxr-x 2 postgres postgres 4096 2005-06-16 11:33 .
drwxr-xr-x 14 root root 4096 2005-06-16 11:47 ..
srwxrwxrwx 1 postgres postgres 0 2005-06-16 13:38 .s.PGSQL.5432
-rw------- 1 postgres postgres 34 2005-06-16 13:38 .s.PGSQL.5432.lock
murrayc@ubuntumurrayc:~$
murrayc@ubuntumurrayc:~$
murrayc@ubuntumurrayc:~$ sudo head -n 1 /var/lib/postgresql/7.4/main/postmaster.pid
Password:
8966
murrayc@ubuntumurrayc:~$
murrayc@ubuntumurrayc:~$
murrayc@ubuntumurrayc:~$ ps aux|grep postmaster
postgres 8966 0.0 0.4 18408 2280 ? S 11:33 0:00
/usr/lib/postgresql/7.4/bin/postmaster -D /var/lib/postgresql/7.4/main
murrayc 18268 0.0 0.1 3024 764 pts/0 R+ 13:40 0:00 grep postmaster
murrayc@ubuntumurrayc:~$

[Martin Pitt (pitti)]

(In reply to comment #8)
> /var/run/postgresql:
> total 12
> drwxrwxr-x 2 postgres postgres 4096 2005-06-16 11:33 .
> drwxr-xr-x 14 root root 4096 2005-06-16 11:47 ..
> srwxrwxrwx 1 postgres postgres 0 2005-06-16 13:38 .s.PGSQL.5432

Hm, everything looks alright. So the socket is in /var/run/postgresql/ (where it
belongs to), but sudo -u postgres psql -l tries to connect to the socket in
/tmp. This might have been a bug in very old packages, so can you please make
sure you use the latest versions?

What is the version of libpq3, postgresql-client-7.4 and postgresql-common? You
can find it out with

  dpkg -s <package name> | grep ^Version:

[Martin Pitt (pitti)]

BTW, for cross-checking, please confirm that

 sudo -u postgres psql -h /var/run/postgres -l

indeed works.

[Murray Cumming (murrayc)]

murrayc@ubuntumurrayc:~$ dpkg -s libpq3 | grep ^Version
Version: 1:7.4.8-9
murrayc@ubuntumurrayc:~$ dpkg -s postgresql-client-7.4 | grep ^Version
Version: 1:7.4.8-9
murrayc@ubuntumurrayc:~$ dpkg -s postgresql-common | grep ^Version
Version: 15
murrayc@ubuntumurrayc:~$
murrayc@ubuntumurrayc:~$ sudo -u postgres psql -h /var/run/postgres -l
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/postgres/.s.PGSQL.5432"?

[Murray Cumming (murrayc)]

Maybe you meant this:

murrayc@ubuntumurrayc:~$ sudo -u postgres psql -h /var/run/postgresql -l
        List of databases
   Name | Owner | Encoding
-----------+----------+----------
 template0 | postgres | UNICODE
 template1 | postgres | UNICODE
(2 rows)

[Martin Pitt (pitti)]

(In reply to comment #11)

> murrayc@ubuntumurrayc:~$ dpkg -s postgresql-common | grep ^Version
> Version: 15

(In reply to comment #12)
> Maybe you meant this:
> murrayc@ubuntumurrayc:~$ sudo -u postgres psql -h /var/run/postgresql -l

Erm, yes, of course. Sorry.

Thanks for confirming that. This bug was fixed in postgresql-common version 16
which should be automatically synced to Breezy by tomorrow. See
http://bugs.debian.org/cgi-bin/bugreport.cgi?bug=313651 for the details.

In the meantime, you can either fix this by explicitly setting

  unix_socket_directory = '/var/run/postgresql'

in /etc/postgresql/7.4/main/postgresql.conf or downloading

http://ftp.de.debian.org/debian/pool/main/p/postgresql-common/postgresql-common_16_all.deb

and installing it with

  sudo dpkg -i postgresql-common_16_all.deb

I'll close this bug when -16 enters Breezy.

Thanks for your help!

[Martin Pitt (pitti)]

(In reply to comment #13)

> I'll close this bug when -16 enters Breezy.

Version 16 is in Breezy, closing.