[imlinuxed]

Im kind of wondering what went wrong with my postgres server. It gave me some headache to get it running. I could not start it with "pg_ctl start". Somehow I use "service postgres start" and it worked. After running it for about a month with good number of users, I woke up one morning to find out I can no longer login to the database with phpPgAdmin interface and my application that is using the database cannot connect to the database again.
When I login in phpPgAdmin it tells me login fail. I still use service postgres start to start the postgres server. My major aim is to backup the database. Can someone assist me on how to go about this?
Im wondering maybe because I have two directory installation one in /usr/local/pgsql/data for 8.22 and the other in /var/lib/pgsql/ for 7.2
Please advice me how I can jsut get my database backup.

[loquin]

Note: I'm not a linux user...

What is the maximum number of logons allowed in the configuration files? (MAX_CONNECTIONS in postgresql.conf) What is the value of SUPERUSER_RESERVED_CONNECTIONS?

How many are currently logged on? (I'm sure that linux has the ability to view the running threads/processes)

If you have an application that is not releasing the connection, you will eventually 'run out' of connections...

After rebooting the server are you able to log on?

[amthomas]

You can probably do something like this while running as root:

netstat -tp | grep postgresql

you might want to double check your pg_hba file to make sure it is correct. That will definitely stop you if it has been messed with

I haven't run two dbms instances on the same machine before so I can't advise anything there. I imagine the just run independently though so it shouldn't be an issue. Just check the configs for each.

What have you tried to backup the db? Have you just used the web admin interfaces to do that in the past? (I am guessing this is the case)

You can get on the machine and sudo over to the postgres user. Then you can run the backup commands for each db or each db cluster.

I guess you just want to backup the individual dbs. Something like this works, but you should double check the manual to make sure its what you want.. (This is in reference to 8.2 mostly. I am not sure if it is similar with 7.2 but I -think- the programs will handle older version, but I am not sure)

This is a very basic DB backup.

pg_dump -d <dbname> -f <fileout>

You will need to run pg_restore to put the data back.

pg_restore -d <dbname> < <filein>

But those only backup individual dbs.. if you have multiple dbs running on the cluster then you could do a pg_dumpall

pg_dumpall > <outfile>

Since you are running different versions you should look in the manuals for each to make sure there are no little problems in doing backups I simply can't warn you about what you might encounter here.

My guess is that a config file is just wonky and once you set it correctly it will work. Or, as loquin suggests, check you connections to make sure they are bieng released properly. I have noticed on our server (Ubuntu) that every once in awhile a tcp connection will stay open when it shouldn't. We are not sure of the cause, but we kill off the process and it goes away. This hasn't caused any issues for us since it is rare and usually only 1 zombie connection.

If you get any more diagnostic info post it and we can take a look. I am new to a lot of this stuff myself, so I will try to help, but PLEASE look in the documentation hehe.

8.2
7.2

Edit: forgot to add that I think the command you want is pg_ctlcluster (although I am not sure if this works for 7.2). I usually just do ./init.d/postgresql-V.X reload/restart/stop hehe