Thread: Cannot start the postgres service
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. -- View this message in context: http://www.nabble.com/Cannot-start-the-postgres-service-tp25867194p25867194.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
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?)
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.
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 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
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.
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: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.