How to configure pgsql in such a way that it is always recoverable?

RP Khare <passionate_programmer@hotmail.com> wrote:

Hi,

I migrated a MySQL database to pgsql 9.0.1. I have never used pgsql before and
therefore want to learn all the possible ways to keep the database up and

Welcome.

running. Following are my queries:

1. If pgsql service fails to start or due to any reason pgsql database is not
connecting, how to restart the service. Where to check for errors?

Depends on your operating system. Usually

/etc/init.d/postgresql start

You can see any errors in your logfile. Where is the logfile? Right,
depends on your OS and configuration.

2. Imagine a scenario where there is no backup of the database and all I have

That's bad ...

is the present installation. I want to move my existing database to another
PC where a fresh installation of pgsql awaits. Which are those files that
need to be moved and how to attach these files on the other installation?

You can use psql (the CLI for PostgreSQL), within:

test=*# show data_directory;
data_directory
------------------------------
/var/lib/postgresql/8.4/main

You need ALL the files below. You need also the same version of PG!
But that's the wrong way, the right way is a regular backup. See below!

3. I want to enable all types of Redo logs like things and all types of backup
options. I also want to take automatic scheduled backups. How to do this?

Read the documentation, there are a chapter about backup and restore.
There are several ways, pg_dump, pg_dumpall (with diffent options) and
PITR (point in time recovery). Too much to explain all here...

Btw.: we don't have a 'repair' - command for tables. It's a feature, not
a bug ...

Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect. (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly." (unknown)
Kaufbach, Saxony, Germany, Europe. N 51.05082�, E 13.56889�