Thread: Postgres file structure doubt
Hi everyone,
Got a doubt in my setup, please correct me if I'm wrong.
In my postgres setup,
/usr/local/pgsql (where postgres install)
/usr/local/pgsql/data (PGDATA)
/database/pg/mydata (tablespace which use for all the table I create)
/database/pg/myindex (index which use for all the table I create)
1) In this setup, the actual user data are store in PGDATA
the table structure & index are store in /database/pg/mydata & /database/pg/myindex
Am I correct?
2) So to backup (not pg_dump), I should make sure it include these 2 folder right?
/usr/local/pgsql/data
/database/pg/
3) I think my setup is not quite right, I should move the PGDATA to /database/pg right?
Regards
Louis
On Nov 19, 2007 11:24 AM, <mailtolouis2020-postgres@yahoo.com> wrote: > > Hi everyone, > > Got a doubt in my setup, please correct me if I'm wrong. > > In my postgres setup, > /usr/local/pgsql (where postgres install) > /usr/local/pgsql/data (PGDATA) > /database/pg/mydata (tablespace which use for all the table I create) > /database/pg/myindex (index which use for all the table I create) > > 1) In this setup, the actual user data are store in PGDATA > the table structure & index are store in /database/pg/mydata & > /database/pg/myindex > > Am I correct? The data that defines users, and tables, and other objects are in PGDATA. The data from users are stored in mydata/myindex. Not sure if that matches what you wrote or not... > 2) So to backup (not pg_dump), I should make sure it include these 2 folder > right? > > /usr/local/pgsql/data > /database/pg/ To backup, you should generally use pg_dump. Are you planning on using PITR? Are you planning on shutting down your database when you back it up? if you're not using PITR, you must shut down postgresql to take a file system backup. > 3) I think my setup is not quite right, I should move the PGDATA to > /database/pg right? Sorta a personaly choice really.
Hello Scott,
Thanks for clear my doubt. Yes, I'm planning to do PITR backup.
Another question, from what I understand, when there are data transaction going on, postgres will store in the log file, which is in /usr/local/pgsql/data/pg_xlog, when these data will finally save it into /database/pg/mydata?
For e.g, if I did a pg_dump backup a week ago, and the system crash today, but I'm able to get all the files in /usr/local/pgsql/data/pg_xlog, can I still recover my database to the latest state?
Regards
Louis
----- Original Message ----
From: Scott Marlowe <scott.marlowe@gmail.com>
To: mailtolouis2020-postgres@yahoo.com
Cc: pgsql-general@postgresql.org
Sent: Monday, November 19, 2007 5:38:12 PM
Subject: Re: [GENERAL] Postgres file structure doubt
On Nov 19, 2007 11:24 AM, <mailtolouis2020-postgres@yahoo.com> wrote:
>
> Hi everyone,
>
> Got a doubt in my setup, please correct me if I'm wrong.
>
> In my postgres setup,
> /usr/local/pgsql (where postgres install)
> /usr/local/pgsql/data (PGDATA)
> /database/pg/mydata (tablespace which use for all the table I create)
> /database/pg/myindex (index which use for all the table I create)
>
> 1) In this setup, the actual user data are store in PGDATA
> the table structure & index are store in /database/pg/mydata &
> /database/pg/myindex
>
> Am I correct?
The data that defines users, and tables, and other objects are in
PGDATA. The data from users are stored in mydata/myindex. Not sure
if that matches what you wrote or not...
> 2) So to backup (not pg_dump), I should make sure it include these 2 folder
> right?
>
> /usr/local/pgsql/data
> /database/pg/
To backup, you should generally use pg_dump. Are you planning on
using PITR? Are you planning on shutting down your database when you
back it up? if you're not using PITR, you must shut down postgresql
to take a file system backup.
> 3) I think my setup is not quite right, I should move the PGDATA to
> /database/pg right?
Sorta a personaly choice really.
---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings
From: Scott Marlowe <scott.marlowe@gmail.com>
To: mailtolouis2020-postgres@yahoo.com
Cc: pgsql-general@postgresql.org
Sent: Monday, November 19, 2007 5:38:12 PM
Subject: Re: [GENERAL] Postgres file structure doubt
On Nov 19, 2007 11:24 AM, <mailtolouis2020-postgres@yahoo.com> wrote:
>
> Hi everyone,
>
> Got a doubt in my setup, please correct me if I'm wrong.
>
> In my postgres setup,
> /usr/local/pgsql (where postgres install)
> /usr/local/pgsql/data (PGDATA)
> /database/pg/mydata (tablespace which use for all the table I create)
> /database/pg/myindex (index which use for all the table I create)
>
> 1) In this setup, the actual user data are store in PGDATA
> the table structure & index are store in /database/pg/mydata &
> /database/pg/myindex
>
> Am I correct?
The data that defines users, and tables, and other objects are in
PGDATA. The data from users are stored in mydata/myindex. Not sure
if that matches what you wrote or not...
> 2) So to backup (not pg_dump), I should make sure it include these 2 folder
> right?
>
> /usr/local/pgsql/data
> /database/pg/
To backup, you should generally use pg_dump. Are you planning on
using PITR? Are you planning on shutting down your database when you
back it up? if you're not using PITR, you must shut down postgresql
to take a file system backup.
> 3) I think my setup is not quite right, I should move the PGDATA to
> /database/pg right?
Sorta a personaly choice really.
---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings
On Tue, 2007-11-20 at 08:38 -0800, mailtolouis2020-postgres@yahoo.com wrote: > > For e.g, if I did a pg_dump backup a week ago, and the system crash > today, but I'm able to get all the files > in /usr/local/pgsql/data/pg_xlog, can I still recover my database to > the latest state? No, pg_dump never allows any form of Point in Time Recovery. -- Simon Riggs 2ndQuadrant http://www.2ndQuadrant.com