Re: Doubt in Backup

Dear all,

2 days ago, I need to backup 2 databases in my Database server because I
need to format the system and reinstalls again with the back up databases.

After a fresh install of ubuntu-10.4 , I install postgreplus-8.4 binary
and I think giving the previous data directory /hdd2-1/postgres_data
during installation for my own testing.

I think it picks the previous data and therefore, no need for restoring
backups of 2 databases that was taken before.

But what I have noticed that there is 6 databases in my Postgres
database and one database *pdc_uima* doesn't have any entry in \l command.

However I restore it from my previous backup.

Does Someone has any comments and suggestion on it. What is the reason
of this strange problem.

Thanks & best Regards,
Adarsh Sharma

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On Mon, Mar 21, 2011 at 11:43:57AM +0530, Adarsh Sharma wrote:
> Dear all,
>
> 2 days ago, I need to backup 2 databases in my Database server because I
> need to format the system and reinstalls again with the back up databases.
>
> After a fresh install of ubuntu-10.4 , I install postgreplus-8.4 binary and
> I think giving the previous data directory /hdd2-1/postgres_data during
> installation for my own testing.
>
> I think it picks the previous data and therefore, no need for restoring
> backups of 2 databases that was taken before.

*If* the versions of the old and new PostgreSQL don't differ very wildly
this will most probably work, yes (well: you'd also run into problems
with different processor architectuures).

> But what I have noticed that there is 6 databases in my Postgres database
> and one database *pdc_uima* doesn't have any entry in \l command.

Hm. I don't understand: you re-used the data directory for the new
installation and the new installation lost one database?

> However I restore it from my previous backup.

and after the restore it was there again? That's definitely strange.

Regards
- -- tomás
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)

iD4DBQFNhw1tBcgs9XrR2kYRAl1UAJEBCs9NBQLsGJLy1Dxm2OElCYIEAJ4y7N4d
GtEFnsrstDjASx+6OuY7Yw==
=Kg5b
-----END PGP SIGNATURE-----

On Mar 21, 2011, at 11:43 AM, Adarsh Sharma wrote:

>
> But what I have noticed that there is 6 databases in my Postgres database and one database pdc_uima doesn't have any entry in \l command.
>
> However I restore it from my previous backup.

That should not happen. If the Binaries version is same and Data Directory is same, then \l should show all databases.

Please reconfirm the data directory and Version of PG.

Thanks & Regards,
Vibhor Kumar
EnterpriseDB Corporation
The Enterprise PostgreSQL Company
vibhor(dot)kumar(at)enterprisedb(dot)com
Blog:http://vibhork.blogspot.com

On Sunday, March 20, 2011 11:13:57 pm Adarsh Sharma wrote:
> Dear all,
>
> 2 days ago, I need to backup 2 databases in my Database server because I
> need to format the system and reinstalls again with the back up databases.
>
> After a fresh install of ubuntu-10.4 , I install postgreplus-8.4 binary
> and I think giving the previous data directory /hdd2-1/postgres_data
> during installation for my own testing.

Is this the same major version you where running before? In other word where you
running 8.4.x previously?

>
> I think it picks the previous data and therefore, no need for restoring
> backups of 2 databases that was taken before.

This I wonder about. When you said reformat above, did you mean reformat the file
system or did it have some other meaning?

>
> But what I have noticed that there is 6 databases in my Postgres
> database and one database *pdc_uima* doesn't have any entry in \l command.

Well there are the three system databases,template0,template1 and postgres. If
it did indeed install over the previous $DATA directory then that would be an
additional two to get you to five. Showing the list of database and what is
expected and what is not would be helpful.

>
> However I restore it from my previous backup.
>
> Does Someone has any comments and suggestion on it. What is the reason
> of this strange problem.

When you reinstalled Postgres did you do a initdb to create a new cluster?

>
>
> Thanks & best Regards,
> Adarsh Sharma

--
Adrian Klaver
adrian(dot)klaver(at)gmail(dot)com

> Dear all,
>
> 2 days ago, I need to backup 2 databases in my Database server because I need to format the system and reinstalls again with the back up databases.
>
> After a fresh install of ubuntu-10.4 , I install postgreplus-8.4 binary and I think giving the previous data directory /hdd2-1/postgres_data during installation for my own testing.
>

You really shouldn't backup the filesystem to backup your database. There are many situations where it will not work and you will shoot yourself in the foot. You should really use pg_dump or pg_dumpall. See http://www.postgresql.org/docs/9.0/static/backup.html and http://www.postgresql.org/docs/9.0/static/backup-file.html for more information.

> I think it picks the previous data and therefore, no need for restoring backups of 2 databases that was taken before.
>
> But what I have noticed that there is 6 databases in my Postgres database and one database pdc_uima doesn't have any entry in \l command.
>
> However I restore it from my previous backup.
>
> Does Someone has any comments and suggestion on it. What is the reason of this strange problem.
>
>
> Thanks & best Regards,
> Adarsh Sharma