Thread: backup and restore
hi all I am developing web site use database is PostGres and now i must build function backup and Restore database on Web My web develop on framework 2.0(asp.net 2.0) I know have two file on forder bin use backup and restore is: pg_restore.exe , pg_dump.exe I have plan is write function or procedures on Database can backup and restore database. How can i write this function ??? Some body show for me? -- View this message in context: http://www.nabble.com/backup-and-restore-tf3714247.html#a10389799 Sent from the PostgreSQL - general mailing list archive at Nabble.com.
On 09.05.2007 09:45, anhtin wrote: > hi all > I am developing web site use database is PostGres and now i must build > function backup and Restore database on Web > My web develop on framework 2.0(asp.net 2.0) > I know have two file on forder bin use backup and restore is: pg_restore.exe > , pg_dump.exe > I have plan is write function or procedures on Database can backup and > restore database. > How can i write this function ??? > Some body show for me? Well, a good start might be to take a look at the manual of the application in question? See http://www.postgresql.org/docs/current/static/backup.html -- Regards, Hannes Dorbath
thanks reply for me but have propblem: the function pg_start_backup() i dont run this function Can u send me example is a procedure or function can backup and restore for me ???? -- View this message in context: http://www.nabble.com/backup-and-restore-tf3714247.html#a10391372 Sent from the PostgreSQL - general mailing list archive at Nabble.com.
Anybody show for me ????? i want backup database and i read on Internet have function pg_start_backup(C:\Program Files\MicrosoftSQLServer\MSSQL\BACKUP\abc.backup) but i not run Some body show me. How will i do run this function ?? -- View this message in context: http://www.nabble.com/backup-and-restore-tf3714247.html#a10426698 Sent from the PostgreSQL - general mailing list archive at Nabble.com.
Anybody show for me ????? i want backup database and i read on Internet have function pg_start_backup(C:\Program Files\MicrosoftSQLServer\MSSQL\BACKUP\abc.backup) but i not run Some body show me. How will i do run this function ?? -- View this message in context: http://www.nabble.com/backup-and-restore-tf3714247.html#a10426702 Sent from the PostgreSQL - general mailing list archive at Nabble.com.
Anybody show for me ????? i want backup database and i read on Internet have function pg_start_backup(C:\Program Files\MicrosoftSQLServer\MSSQL\BACKUP\abc.backup) but i not run Some body show me. How will i do run this function ?? -- View this message in context: http://www.nabble.com/backup-and-restore-tf3714247.html#a10426704 Sent from the PostgreSQL - general mailing list archive at Nabble.com.
anhtin wrote: > Anybody show for me ????? > i want backup database and i read on Internet have function > pg_start_backup(C:\Program Files\MicrosoftSQLServer\MSSQL\BACKUP\abc.backup) > but i not run > Some body show me. How will i do run this function ?? See: http://www.postgresql.org/docs/8.2/static/continuous-archiving.html -- Paul Lambert Database Administrator AutoLedgers
anhtin wrote: > Anybody show for me ????? > i want backup database and i read on Internet have function > pg_start_backup(C:\Program Files\MicrosoftSQLServer\MSSQL\BACKUP\abc.backup) > but i not run > Some body show me. How will i do run this function ?? Read the documentation! http://www.postgresql.org/docs/current/static/backup-dump.html pg_start_backup is NOT for creating full backups - it is for creating partial backups (as you would see here: http://www.postgresql.org/docs/8.2/static/continuous-archiving.html). -- Postgresql & php tutorials http://www.designmagick.com/
Thank reply for me Can u show me How will i backup database on Function or proc on Database?? -- View this message in context: http://www.nabble.com/backup-and-restore-tf3714247.html#a10427265 Sent from the PostgreSQL - general mailing list archive at Nabble.com.
On 14 jan, 10:36, hiddenhippo <reda...@gmail.com> wrote: > Having recently jumped ship from MSSQL to Postgres I'm finding it a > little difficult getting onto the 'correct path.' Basically I have a > live database and I want to take a copy of that and then restore it > onto a backup server. > > Firstly I'm using Postgres 8.2 and pgAdmin 1.6.3 > > Within the pgAdmin interface I've selected the database I wish to > backup, given it a filename, selected 'Compress' and have chosen blobs > and OIDs. From the documentation it appears that if you use foreign > keys then you should use OIDs. Anyway, the backup appears to work > fine and a file is created. No errors are displayed at any point. > > When I come to restore the database I fistly create a blank one, and > then right click and choose restore. The problem is that errors are > thrown because of foreign-key constraints. > > With a little more investigation I changed the backup output to > 'plain' so that I could see the script generated. It appears that the > backup process is created tables and their associated data in the > wrong order. For example, at the top of the generated file it > attempts to insert data into a given table, however the table dictates > that some entries must have a corresponding entry in another, for > example a user id (a foreign key saying that UID value must exist on > the user tables primary key). After detailing the data for the first > table it then details the data that should go into the parent table, > or in my example above, it then attempts to populate the user table. > When you run the restore process this it fails because the user table > doesn't have the corresponding entries. > > Could someone please suggest why this is happening and how, if it can > be done, I fix it? > > Thanks It's quite simple to have problems with pgAdmin's backup procedure. Althought pgAdmin 3 uses pg_dump and pg_restore utilities, I could report many errors when did backups and/or restores from it. I suggest you to use pg_dump command line utility into a plain SQL file, so you can compress it with bzip2 to maximum compress rates. When you restore, decompress the file (if you have packed it) and use the psql to load the script file into the new and clean database.
T.J. Adami wrote: > On 14 jan, 10:36, hiddenhippo <reda...@gmail.com> wrote: >> Having recently jumped ship from MSSQL to Postgres I'm finding it a >> little difficult getting onto the 'correct path.' Basically I have a >> live database and I want to take a copy of that and then restore it >> onto a backup server. >> >> Firstly I'm using Postgres 8.2 and pgAdmin 1.6.3 OK. Latest is 8.2.6 I believe, make sure you keep up to date with minor updates. Oh, and pgAdmin has released version 1.8 now, although that shouldn't affect backups. >> Within the pgAdmin interface I've selected the database I wish to >> backup, given it a filename, selected 'Compress' and have chosen blobs >> and OIDs. From the documentation it appears that if you use foreign >> keys then you should use OIDs. You probably want "compress" - that format allows you do partial restores as well as being compressed. I can't tell whether you've got any blobs ("large objects" in PG terminology). I don't think you'll need to dump OIDs unless you're explicitly using them. The manuals have recommended against that for some years now, so if you can remember which bit gave you the impression you need them for foreign keys then that would be useful. > Anyway, the backup appears to work >> fine and a file is created. No errors are displayed at any point. Excellent. You should also have a "messages" tab that shows the pg_dump command it uses. You can run the same from the command-line to dump your database. If you've got "verbose messages" turned on then it will name each item as it dumps it too. I'm working from pgAdmin 1.8 here, but I don't think it was any different in 1.6 >> When I come to restore the database I fistly create a blank one, and >> then right click and choose restore. The problem is that errors are >> thrown because of foreign-key constraints. OK, that's down to one of three things: 1. The database wasn't empty - perhaps you had a partial restore in-place. Unlikely, since I'm sure you repeated your steps above. 2. The dump was in plain-text and you were doing separate schema + data restores. We can rule this out because you've said otherwise. 3. You aren't restoring as a super-user (typically "postgres"), so it can't disable + re-enable foreign keys. That's what I think is happening. >> With a little more investigation I changed the backup output to >> 'plain' so that I could see the script generated. It appears that the >> backup process is created tables and their associated data in the >> wrong order. For example, at the top of the generated file it >> attempts to insert data into a given table, however the table dictates >> that some entries must have a corresponding entry in another, for >> example a user id (a foreign key saying that UID value must exist on >> the user tables primary key). After detailing the data for the first >> table it then details the data that should go into the parent table, >> or in my example above, it then attempts to populate the user table. >> When you run the restore process this it fails because the user table >> doesn't have the corresponding entries. If you look more carefully, you should see that all the foreign keys are created at the bottom of the file after the data restore. >> Could someone please suggest why this is happening and how, if it can >> be done, I fix it? >> >> Thanks > > It's quite simple to have problems with pgAdmin's backup procedure. Hmm - shouldn't be, and if so then please let the pgAdmin people know! They're always working to improve the package and it's > Althought pgAdmin 3 uses pg_dump and pg_restore utilities, I could > report many errors when did backups and/or restores from it. Did you keep any notes, and can you reproduce them? > I suggest you to use pg_dump command line utility into a plain SQL > file, so you can compress it with bzip2 to maximum compress rates. > When you restore, decompress the file (if you have packed it) and use > the psql to load the script file into the new and clean database. You're better off using the compressed mode imnsho. The flexibility is well worth any minor loss vs bzip2's compression. Check out the -l / -L options to let you pick and choose what you restore. -- Richard Huxton Archonet Ltd
On 15 jan, 12:29, d...@archonet.com (Richard Huxton) wrote: > > It's quite simple to have problems with pgAdmin's backup procedure. > > Hmm - shouldn't be, and if so then please let the pgAdmin people know! > They're always working to improve the package and it's > I think the same. We (company I work for) develop an ERP software with more than 200 customers, and each one have a individual and confidential database with PostgreSQL. Using pgAdmin version 1.6.x or lower with those databases causes errors when the database is restored from a .backup file created with compression from pgAdmin. However, to kill the doubt, I realize those errors with pg_restore utility from terminal as well. > > Althought pgAdmin 3 uses pg_dump and pg_restore utilities, I could > > report many errors when did backups and/or restores from it. > > Did you keep any notes, and can you reproduce them? In fact, I do not keep notes from those errors because they're always on database schema. The table and its data can be restored very well. So we have a utility written in Visual Foxpro wich can read postgres database schema and do the database diff to upgrade the restored db with the schema needed (PKs, FKs, views, triggers, etc). I will study these cases most extensively in our labs, and will take notes to post them here (once I can prove it's not a pgAdmin problem but pg_dump issue when using compressing - if not proved, I send it to pgAdmin developers.). > > I suggest you to use pg_dump command line utility into a plain SQL > > file, so you can compress it with bzip2 to maximum compress rates. > > When you restore, decompress the file (if you have packed it) and use > > the psql to load the script file into the new and clean database. > > You're better off using the compressed mode imnsho. The flexibility is > well worth any minor loss vs bzip2's compression. Check out the -l / -L > options to let you pick and choose what you restore. The only way I found to dump and restore databases with no errors is using pg_dump creating a plain text file. Compression is a good way, but I prefer create a plain text SQL file and compress it my way after... And maybe bzip2 couldn't be the best pratice, it was just a suggestion ;) P.S: I have to trace exactly these pgAdmin issues. The issues really exists (I'll not post any fake message here if it was not true), but I can't send databases dump to reproduce them according the contract with our customers (confidential data).
shall be sprinkled on his garments, so as to stain all his raiment. He will not only hate you, but he will have you in the utmost contempt: no place shall be thought fit for you, but under his feet to be trodden down as the mire of the streets. The misery you are exposed to is that which God will inflict to that end, that he might show what that wrath of Jehovah is. God hath had it on his heart to show to angels and men, both how excellent his love is, and also how terrible his wrath is. Sometimes earthly kings have a mind to show how terrible their wrath is, by the extreme punishments they would execute on those that would provoke them. Nebuchadnezzar, that mighty and haughty monarch of the Chaldean empire, was willing to show his wrath when enraged with Shadrach, Meshach, and Abednego; and accordingly gave orders that the burning fiery furnace should be heated seven times hotter than it was before; doubtless, it was raised to the utmost degree of fierceness that human art could raise it. But the great God is also willing to show his wrath, and magnify his awful majesty and mighty power in the extreme sufferings of his enemies. Rom. 9:22. "What if God, willing to show his wrath, and to make his power known, endured with much long-suffering the vessels of wrath fitted to destruction?" And seeing this is his design, and what he has determined, even to show how terrible the unrestrained wrath, the fury and fierceness of Jehovah is, he will do it to effect. There will be something accomplished and brought to pass that will be dreadful with a witness. When the great and angry God hath risen up and executed his awful vengeance on the poor sinner, and the wretch is actually su
the others in their vastness. For who will not be astounded at the fact that our body, which a little while ago was imperceptible in the universe, itself imperceptible in the bosom of the whole, is now a colossus, a world, or rather a whole, in respect of the nothingness which we cannot reach? He who regards himself in this light will be afraid of himself, and observing himself sustained in the body given him by nature between those two abysses of the Infinite and Nothing, will tremble at the sight of these marvels; and I think that, as his curiosity changes into admiration, he will be more disposed to contemplate them in silence than to examine them with presumption. For, in fact, what is man in nature? A Nothing in comparison with the Infinite, an All in comparison with the Nothing, a mean between nothing and everything. Since he is infinitely removed from comprehending the extremes, the end of things and their beginning are hopelessly hidden from him in an impenetrable secret; he is equally incapable of seeing the Nothing from which he was made, and the Infinite in which he is swallowed up. What will he do then, but perceive the appearance of the middle of things, in an eternal despair of knowing either their beginning or their end. All things proceed from the Nothing, and are borne towards the Infinite. Who will follow these marvellous processes? The Author of these wonders understands them. None other can do so. Through failure to contemplate these Infinites, men have rashly rushed into the examination of nature, as though they bore some proportion to her. It is strange that they have wished to understand the beginnings of things, and thence to arrive at the knowledge of the whole, with a presumption as infinite as their object. For surely this design cannot be formed without presumption or without a ca
T.J. Adami wrote: > On 15 jan, 12:29, d...@archonet.com (Richard Huxton) wrote: >>> It's quite simple to have problems with pgAdmin's backup procedure. >> Hmm - shouldn't be, and if so then please let the pgAdmin people know! >> They're always working to improve the package and it's >> > > I think the same. We (company I work for) develop an ERP software with > more than 200 customers, and each one have a individual and > confidential database with PostgreSQL. > Using pgAdmin version 1.6.x or lower with those databases causes > errors when the database is restored from a .backup file created with > compression from pgAdmin. However, to kill the doubt, I realize those > errors with pg_restore utility from terminal as well. What puzzles me is that as far as I know, pgAdmin *uses* pg_dump and pg_restore to handle its backup needs. > P.S: I have to trace exactly these pgAdmin issues. The issues really > exists (I'll not post any fake message here if it was not true), but I > can't send databases dump to reproduce them according the contract > with our customers (confidential data). I'm sure that will be appreciated. Open source projects rely on knowledgable users who are prepared to do a little investigating. It's what helps drive reliability up. -- Richard Huxton Archonet Ltd
On 16/01/2008, Richard Huxton <dev@archonet.com> wrote: > T.J. Adami wrote: > What puzzles me is that as far as I know, pgAdmin *uses* pg_dump and > pg_restore to handle its backup needs. It does. Make sure it's using the correct version for your server though - that's the only reason it might fail that I can think of. In 1.6 it'll use the first version it finds. In 1.8, you can select the path yourself if you like. Regards, Dave