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

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 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?
2. Imagine a scenario where there is no backup of the database and all I have 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?
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?

Regards,
Rohit P. Khare

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°