Cannot start the postgres service

Mitesh51 wrote:

If I move any file from the \PostgreSQL\8.4\data\pg_xlog\ then postgres
services are stopped & I cannot start them. I need to reinstall the
postgres.

well, then don't move any file from pg_xlog while postgres is running!

(was there a question here?)

Yeah...

My query is...Is it the reason y postgres stops working?? (Moving files from
pg_xlog)

John R Pierce wrote:

Mitesh51 wrote:

If I move any file from the \PostgreSQL\8.4\data\pg_xlog\ then postgres
services are stopped & I cannot start them. I need to reinstall the
postgres.

well, then don't move any file from pg_xlog while postgres is running!

(was there a question here?)

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

--
View this message in context: http://www.nabble.com/Cannot-start-the-postgres-service-tp25867194p25868045.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

On Tue, Oct 13, 2009 at 12:14 AM, John R Pierce <pierce@hogranch.com> wrote:

Mitesh51 wrote:

If I move any file from the \PostgreSQL\8.4\data\pg_xlog\ then postgres
services are stopped & I cannot start them. I need to reinstall the
postgres.

well, then don't move any file from pg_xlog while postgres is running!

Yeah, a good general rule of thumb is to not mess about with things if
you're not sure what they do. pg_xlog and pg_clog are definitely in
that category.

On 13/10/2009 2:59 PM, Mitesh51 wrote:

Yeah...

My query is...Is it the reason y postgres stops working?? (Moving files from
pg_xlog)

pg_xlog contains transaction logs. These aren't "log files" in the sense
of text logs designed for the administrator to use. They're part of the
critical function of the database and they're what permits Pg to support
transactional rollback, safe crash recovery, point-in-time recovery, and
lots more.

Deleting them or moving them is just as bad for your database as
deleting or moving the files that store tables. It's a really, really
bad idea.

A better question might by "why on earth are you messing about with the
data directory when you don't understand what it does and how it works?".

--
Craig Ringer

Hi,

I like that "why on earth are you messing about with the
data directory when you don't understand what it does and how it works?"

Actully I am not a DB person and having almost ZERO knowledge abt it but I
am working on an application which takes backup(full & incremental) of diff
DB like mysql, postgres...

Now I dont have any support who helps me on DB side & still I need to move
on with my java code so in current situation I am trying my hands on the DB
backups as well since none else has that knowledge so I am doing it by
searching on my own :)

I had 2 approach in my mind...to sync up transaction log files with specific
full backup

1) to keep only time relavent files in pg_xlog dir and move other files to
archive dir with code which is not a good idea as u suggest

2) to copy files from pg_xlog & archive dir(which is used by
archive_command) and move files from the archive dir & not from the pg_xlog
& thus it will be a another direction for solution of inc backup.

Moving of files is done my postgres as well so I guess, from the archive dir
we can do that.

Craig Ringer wrote:

On 13/10/2009 2:59 PM, Mitesh51 wrote:

Yeah...

My query is...Is it the reason y postgres stops working?? (Moving files
from
pg_xlog)

pg_xlog contains transaction logs. These aren't "log files" in the sense
of text logs designed for the administrator to use. They're part of the
critical function of the database and they're what permits Pg to support
transactional rollback, safe crash recovery, point-in-time recovery, and
lots more.

Deleting them or moving them is just as bad for your database as
deleting or moving the files that store tables. It's a really, really
bad idea.

A better question might by "why on earth are you messing about with the
data directory when you don't understand what it does and how it works?".

--
Craig Ringer

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

--
View this message in context: http://www.nabble.com/Cannot-start-the-postgres-service-tp25867194p25885603.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

On Tue, Oct 13, 2009 at 11:24 PM, Craig Ringer
<craig@postnewspapers.com.au> wrote:

A better question might by "why on earth are you messing about with the
data directory when you don't understand what it does and how it works?".

Not that anyone wants to discourage exploring. It's just there are
better ways to go about things than deleting / removing files if
you're not sure what they do.

There's a whole section on internals here:

http://www.postgresql.org/docs/8.4/interactive/storage.html

If we go here:

http://www.postgresql.org/docs/8.4/interactive/storage-file-layout.html

There's a nice section on what each file-type / directory does.

On Tue, Oct 13, 2009 at 11:40 PM, Mitesh51 <mit_bca1@yahoo.com> wrote:

I like that "why on earth are you messing about with the
data directory when you don't understand what it does and how it works?"

Actully I am not a DB person and having almost ZERO knowledge abt it but I
am working on an application which takes backup(full & incremental) of diff
DB like mysql, postgres...

Now I dont have any support who helps me on DB side & still I need to move
on with my java code so in current situation I am trying my hands on the DB
backups as well since none else has that knowledge so I am doing it by
searching on my own :)

I had 2 approach in my mind...to sync up transaction log files with specific
full backup

1) to keep only time relavent files in pg_xlog dir and move other files to
archive dir with code which is not a good idea as u suggest

2) to copy files from pg_xlog & archive dir(which is used by
archive_command) and move files from the archive dir & not from the pg_xlog
& thus it will be a another direction for solution of inc backup.

Moving of files is done my postgres as well so I guess, from the archive dir
we can do that.

There are really three reliable ways to take a coherent backup.

1: pg_dump
2: PITR
3: Snapshots.

What you're trying seems closer to PITR (Point In Time Recovery).
Look it up in the docs see if it makes sense. OTOH, snapshots,
combined with some kind of diff utility (rdiff is nice) an provide
incrementals quite easily. The deltas may be large if your db changes
a lot over time.

The cool thing about rdiff is that the latest bu is a full backup, and
everything else is deltas going back in time. I.e. instead of just
storing a delta, it's applied to the most recent (i.e. full) backup to
bring it forward, then store that and the delta to go backwards
instead of forwards. Snapshotting methodology is important, it has to
make a coherent at an instant in time snapshops or they may not work
properly.

On 14/10/2009 2:29 PM, Scott Marlowe wrote:

On Tue, Oct 13, 2009 at 11:24 PM, Craig Ringer
<craig@postnewspapers.com.au> wrote:

A better question might by "why on earth are you messing about with the
data directory when you don't understand what it does and how it works?".

Not that anyone wants to discourage exploring. It's just there are
better ways to go about things than deleting / removing files if
you're not sure what they do.

Well said.

For that matter, there's no harm going and mangling the data directory
of an install you don't care about either, though I'm not sure I see
what's to be gained by it.

--
Craig Ringer

Mitesh51 wrote:

I had 2 approach in my mind...to sync up transaction log files with specific
full backup

1) to keep only time relavent files in pg_xlog dir and move other files to
archive dir with code which is not a good idea as u suggest

Postgres is prepared to (and assumes it can) reuse and delete files in
pg_xlog. If you need a copy you can use for your own purposes, you MUST
get it through an archive_command. You MUST NOT fiddle with the files
in pg_xlog directly.

Also note that your archive_command needs to create a separate copy of
the file. Hardlinks are not allowed, because the file might get
rewritten by Postgres later. Moving (mv) the original files is not
allowed either for the same reason. Postgres will leave the file alone
until it has been archived, and assumes it can do whatever it pleases
with it as soon as the archiver has returned success (exit code 0).

--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

Let me clarify bit more so I can do thing in proper manner...

I take base backup(full).

at fixed interval I need to take incremental backup and for that...

I use following commands.

psql -U postgres -c "select pg_switch_xlog()"

psql -U postgres -c "SELECT pg_stop_backup()"

after that the WAL file(s) is/are archived & I consider that file as a file
which will be used for backup along with all files exist in the pg_xlog dir.

same approach is mentioned in the
http://www.mkyong.com/database/postgresql-point-in-time-recovery-incremental-backup/

Am I doing things properly??

I know how to restore data from the full backup but havn't done anything to
restore data from the WAL files...Hopefully I will find the way...If u can
emphasize somewhere then it will be a gr8 help :)

Please correct me if I am wrong somewhere :)

Alvaro Herrera-7 wrote:

Mitesh51 wrote:

I had 2 approach in my mind...to sync up transaction log files with
specific
full backup

1) to keep only time relavent files in pg_xlog dir and move other files
to
archive dir with code which is not a good idea as u suggest

Postgres is prepared to (and assumes it can) reuse and delete files in
pg_xlog. If you need a copy you can use for your own purposes, you MUST
get it through an archive_command. You MUST NOT fiddle with the files
in pg_xlog directly.

Also note that your archive_command needs to create a separate copy of
the file. Hardlinks are not allowed, because the file might get
rewritten by Postgres later. Moving (mv) the original files is not
allowed either for the same reason. Postgres will leave the file alone
until it has been archived, and assumes it can do whatever it pleases
with it as soon as the archiver has returned success (exit code 0).

--
Alvaro Herrera
http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

--
View this message in context: http://www.nabble.com/Cannot-start-the-postgres-service-tp25867194p25904095.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.