Re: [ADMIN] Backup&Restore Postgres DB
Hi Scott, I m real new in this so be patient :) I check in postgres and: radius-# \l List of databases Name| Owner | Encoding ---+--+-- postgres | postgres | UTF8 radius| postgres | UTF8 root | postgres | UTF8 template0 | postgres | UTF8 template1 | postgres | UTF8 The database that i real need is radius, i suppose that the others are defaults databases from postgres, i was thinking and probably the best is to backup and restore all since i don't know what they are and well since is to restore... So probably the best is to backup and restore radius with no data (i don't want data), and the rest with data. pg_dumpall -g > users.sql --> it will give ALL needed stuff (usr, permissions,etc) for all DBs? psql -d [database] -f users.sql --> i have to this command for each DBs that i have (5)? supposing that now i have all permissions stuff-- For radius pg_dump -c -C -s radius > radius.out --> structure of the database radius (i probably made confusion with what is a schema before) psql -d radius -f radius.out For all others pg_dump [db_name] > [db_name].out --> this is dumping schema and data, correct? psql -d [db_name]-f [db_name].out --> this is restoring schema and data, correct? i read the manual and there are something called large objects, how do i know if i have that, because the commands are different if i have it. Thanks Scott Mead-3 wrote: > > Lots there, let's break it down individually: > > > On Mon, Mar 22, 2010 at 6:38 AM, blast wrote: > >> >> Hi all, >> >> I need to backup and restore a DB. >> In this particular case the data in the database is not important >> (strange >> hum...) but only the schema to put new data... >> >> I m thinking use the pg_dump: >> >> pg_dump -c -C -s schema > file.out >> > > Almost > >-s says "only dump the structure of the database" >If you want to dump a specific named schema inside the database, you'd > use: > >-n > > So, what you end up with is: > > pg_dump -c -C -s -n [schema] [database] > file.out > > >> >> With this i have in file.out the schema, correct? >> > > Yes > > >> >> So, to restore the db from scratch, for example to a new system, what >> should >> i do? >> > > psql -d [database] -f file.out > > >> it automatically creates all users, rolls, permissions etc?How? >> > > To create users, you need to dump them from the source db first (pg_dump > doesn't do users)... > so > > pg_dumpall -g > users.sql > > psql -d [database] -f users.sql > > do this BEFORE you do the restore of your objects and all your permissions > etc... will be set. > > >> >> What is the best way of doing it? >> >> > > Good luck! > >> >> Many thanks >> >> -- >> View this message in context: >> http://old.nabble.com/Backup-Restore-Postgres-DB-tp27984540p27984540.html >> Sent from the PostgreSQL - admin mailing list archive at Nabble.com. >> >> >> -- >> Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) >> To make changes to your subscription: >> http://www.postgresql.org/mailpref/pgsql-admin >> > > -- View this message in context: http://old.nabble.com/Backup-Restore-Postgres-DB-tp27984540p27998906.html Sent from the PostgreSQL - admin mailing list archive at Nabble.com. -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] Backup&Restore Postgres DB
Lots there, let's break it down individually: On Mon, Mar 22, 2010 at 6:38 AM, blast wrote: > > Hi all, > > I need to backup and restore a DB. > In this particular case the data in the database is not important (strange > hum...) but only the schema to put new data... > > I m thinking use the pg_dump: > > pg_dump -c -C -s schema > file.out > Almost -s says "only dump the structure of the database" If you want to dump a specific named schema inside the database, you'd use: -n So, what you end up with is: pg_dump -c -C -s -n [schema] [database] > file.out > > With this i have in file.out the schema, correct? > Yes > > So, to restore the db from scratch, for example to a new system, what > should > i do? > psql -d [database] -f file.out > it automatically creates all users, rolls, permissions etc?How? > To create users, you need to dump them from the source db first (pg_dump doesn't do users)... so pg_dumpall -g > users.sql psql -d [database] -f users.sql do this BEFORE you do the restore of your objects and all your permissions etc... will be set. > > What is the best way of doing it? > > Good luck! > > Many thanks > > -- > View this message in context: > http://old.nabble.com/Backup-Restore-Postgres-DB-tp27984540p27984540.html > Sent from the PostgreSQL - admin mailing list archive at Nabble.com. > > > -- > Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-admin >
[ADMIN] Backup&Restore Postgres DB
Hi all, I need to backup and restore a DB. In this particular case the data in the database is not important (strange hum...) but only the schema to put new data... I m thinking use the pg_dump: pg_dump -c -C -s schema > file.out With this i have in file.out the schema, correct? So, to restore the db from scratch, for example to a new system, what should i do? it automatically creates all users, rolls, permissions etc?How? What is the best way of doing it? Many thanks -- View this message in context: http://old.nabble.com/Backup-Restore-Postgres-DB-tp27984540p27984540.html Sent from the PostgreSQL - admin mailing list archive at Nabble.com. -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
[ADMIN] backup restore of DB + LO
Hello everybody, I'm trying backup restore of DB with LO. I backuped using as superuser : pg_dump -d -o -F t -b -u database --file=database.tar Because the superuser is not the DB owner, I create the DB on another server (same os ans same version of PGSQL which is 7.4.8 ) : CREATE DATABASE database with owner database_owner encoding='utf8'; Now, I'm trying to restore it to this second server using : pg_restore -U dbadmin -O -x -v -o -d database database.tar But I received : (...) pg_restore: restoring large object OID 2798405 pg_restore: restored 64069 large objects pg_restore: creating COMMENT SCHEMA public pg_restore: creating TABLE jpox_tables pg_restore: restoring data for table "jpox_tables" pg_restore: [tar archiver] could not find header for file 99.dat in tar archive What is the good way to backup/restore db with LO ? Thanks ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [ADMIN] Backup/Restore Views
Your best bet would be to query the system tables. Something like SELECT definition FROM pg_views; On Thu, Nov 03, 2005 at 12:43:47PM -0600, Randall Smith wrote: > What is the most simple way to backup and restore only views? > > Randall > > > ---(end of broadcast)--- > TIP 4: Have you searched our list archives? > > http://archives.postgresql.org > -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 6: explain analyze is your friend
[ADMIN] Backup/Restore Views
What is the most simple way to backup and restore only views? Randall ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[ADMIN] Backup / Restore help
Hi, I've got a problem, I'm hoping someone can help me with. It appears that my hosting provider has "restored" my database as part of a maintenance project. The problem is that the backup didn't include my large objects. I've got an older backup that has all the large objects, but I don't know how to partially restore just the large objects. Neither do I want to overwrite the other data, leaving the database at an earlier snapshot than is absolutely necessary. Is there a way to restore just the large objects from a backup tar created by pg_dump, leaving the rest of the database intact? Thanks, -Ben Mitchell ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [ADMIN] Backup / Restore
Dear Eduardo , What is the most recommended way to backup a PostgreSQL database? pg_dump --disable-triggers -U -a -d -b -D -Fc -Z 9 -f + A file system backup could be done but for this you will have to shutdown PostgreSQL server before filesystem backup is done And to restore it? pg_restore --disable-triggers -U -d IN case of Filesystem backup just stop PostgreSQL and replace the Dumped or Tared file of the filesystem Links http://www.commandprompt.com/ppbook/index.lxp?lxpwrap=x17860%2ehtm And ofcource the PostgreSQL documentation itself -- Best Regards, Vishal Kashyap Director / Lead Software Developer, Sai Hertz And Control Systems Pvt Ltd, http://saihertz.rediffblogs.com [Soon http://www.saihertz.com] Yahoo IM: coeb_college[ a t ]yahoo.com ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[ADMIN] Backup / Restore
What is the most recommended way to backup a PostgreSQL database? And to restore it? I would like to get ALL the data and properties possible, because if I have a problem, and I lost my current database, I can just restore it, and will be all exactly like was before. What is the best way? pg_dump? If yes, what options I need to use? Thanks a lot!! Eduardo S. Fontanetti __ Yahoo! Mail - agora com 100MB de espaço, anti-spam e antivírus grátis! http://br.info.mail.yahoo.com/ ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [ADMIN] backup restore
You ought to be able to just start up Postgres pointing to the existing data. So assuming the data directory is /db, just doing 'postmaster -D /db' should work. Provided, of course, you installed the exact version that was there before, with the same user/uid as before. On Nov 11, 2003, at 9:51 PM, Colm De Barra wrote: Hi I'm in charge of a linux DB server running postgres 7.3.2. The OS disk recently died taking the postgres installation with it but the data directory of postgres was on a seperate SCSI disk and is still OK. I've put in a new OS disk, installed linux on it again, mounted the data disk, and installed postgres up as far as the "make install" stage. Can anyone tell me where to go from here to get postgres to run with the old DB ?? is all the DB structure information stored in the data directory ? Any help would be appreciated Colm ~ If a kid asks where rain comes from, I think a cute thing to tell him is, "God is crying." And if he asks why God is crying, another cute thing to tell him is, "Probably because of something you did." ~ My Website :http://www.angelfire.com/ia/japan/ BoomBox :http://www.b00mb0x.org ~ Andrew Rawnsley President The Ravensfield Digital Resource Group, Ltd. (740) 587-0114 www.ravensfield.com ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[ADMIN] backup restore
(B (B (BHi (BI'm in charge of a linux DB server running postgres (B7.3.2. (BThe OS disk recently died taking the postgres (Binstallation (Bwith it but the data directory of postgres was on a (Bseperate SCSI (Bdisk and is still OK. (B (BI've put in a new OS disk, installed linux on it (Bagain, mounted the data (Bdisk, and installed (Bpostgres up as far as the "make install" stage. (BCan anyone tell me where to go from here to get (Bpostgres to run with the (Bold DB ?? is all the DB structure information (Bstored in the data directory ? (B (BAny help would be appreciated (BColm (B (B~If a kid (Basks where rain comes from, I think a cute thing to tell him is, "God is (Bcrying." And if he asks why God is crying, another cute thing to tell him (Bis, "Probably because of something you did." (B~ (B (B My Website : http://www.angelfire.com/ia/japan/ (BBoomBox : http://www.b00mb0x.org (B (B~
Re: [ADMIN] Backup - Restore (pg-dump)
creid writes: > I recently upgraded to 7.3.4 from 7.3.2. Prior to the upgrade I used > "pg_dump -a > olddatafile" for just data and "pg_dump -s > oldschemafile" > for schema using the pg_dump utility from 7.3.2. After a "successful" > install of 7.3.4, I created the database then went on to "successful" > "psql -f schemafile" and then "psql -f > datafile" and results were as > expected. This is unnecessary. The data formats of 7.3.2 and 7.3.4 are compatible. Just start the new server in place of the old one. > However, when i used the "pg_dump -a newdatafile" and "psql -f newdatafile" > commands from the 7.3.4 version, I get random: > > psql:bcgdata3:491: ERROR: $1 referential integrity violation - key > referenced from customer not found in euser That is an inevitable consequence of dumping schema and data separately. If you dump them together, pg_dump will insert special commands to disable the constraints while loading the data. > However, I successfully update the 7.3.4 database when I "psql -f > olddatafile" using the 7.3.4 psql command. How is that different from what you did above? > What might be my problem and/or solution? I hope I am clear enough for a > positive response. It would be even clearer if you showed us the exact sequence of your commands, not dumbed-down versions. For example, in your case the dump files where clearly not named "newdatafile". Also, where did you install the new and old versions? Are you sure that running just "psql" will get you the right version? -- Peter Eisentraut [EMAIL PROTECTED] ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[ADMIN] Backup - Restore (pg-dump)
Any Help I recently upgraded to 7.3.4 from 7.3.2. Prior to the upgrade I used "pg_dump -a > olddatafile" for just data and "pg_dump -s > oldschemafile" for schema using the pg_dump utility from 7.3.2. After a "successful" install of 7.3.4, I created the database then went on to "successful" "psql -f schemafile" and then "psql -f > datafile" and results were as expected. However, when i used the "pg_dump -a newdatafile" and "psql -f newdatafile" commands from the 7.3.4 version, I get random: psql:bcgdata3:491: ERROR: $1 referential integrity violation - key referenced from customer not found in euser psql:bcgdata3:491: lost synchronization with server, resetting connection psql:bcgdata3:507: ERROR: $1 referential integrity violation - key referenced from billing not found in customer psql:bcgdata3:507: lost synchronization with server, resetting connection psql:bcgdata3:528: ERROR: $1 referential integrity violation - key referenced from shipping not found in customer psql:bcgdata3:528: lost synchronization with server, resetting connection psql:bcgdata3:586: ERROR: $1 referential integrity violation - key referenced from javastore not found in euser psql:bcgdata3:586: lost synchronization with server, resetting connection psql:bcgdata3:628: ERROR: $1 referential integrity violation - key referenced from bcgmodules not found in javastore psql:bcgdata3:628: lost synchronization with server, resetting connection I tried each command on old cluster and new cluster and very same results. However, I successfully update the 7.3.4 database when I "psql -f olddatafile" using the 7.3.4 psql command. What might be my problem and/or solution? I hope I am clear enough for a positive response. Thanks Much C [EMAIL PROTECTED] ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[ADMIN] backup/restore and vacuumdb
I'm doing some migration script from mysql database to an existing postgresql database. Everytime I want to test it, I want to get the postgres "destination" database to the same situation it was before the last script was run. I am doing a backup with pgdump and restoring by droping the database and running the script generated by pgdump. The backup looks like this: pg_dump -h -U -C dbname > /tmp/database.pgdump The restore looks like this: psql -h -U template1 -c "drop database dbname;" psql -h -U template1 < /tmp/database.pgdump But this aproach is taking a long time and the database is becoming slower every time. So: which is the best backup/restore method to apply in this case? And about the performance, I'm using vacuumdb but I don't know when to do it. Any idea? Thanks _ ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[ADMIN] backup / restore problem
Dear all, I am trying to restore a backup of database created by pg_dump. But I got the following error, no matter I use command a or b. Why pg_restore try to create stuff while data only option is given? --- log --- command (a) su -l pgsql -c "pg_restore -d db01 /export/data/subsys/backend/archive/db01-current.tar" command (b) su -l pgsql -c "pg_restore -a -d db01 /export/data/subsys/backend/archive/db01-current.tar" NOTICE: CREATE TABLE/PRIMARY KEY will create implicit index 'acid_event_pkey' for table 'acid_event' Archiver(db): Could not execute query. Code = 7. Explanation from backend: 'ERROR: Relation 'acid_event' already exists --- log --- Thanks for your help, Vincent Chen __ Do You Yahoo!? Yahoo! Greetings - send holiday greetings for Easter, Passover http://greetings.yahoo.com/ ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [ADMIN] backup/restore
This happens on Slackware as well. "Ross J. Reedstrom" wrote: > On Fri, Sep 08, 2000 at 04:13:20PM -0700, Lindell Alderman wrote: > > I am trying to backup and restore a database, but when run the following: > > > > dropdb test > > createdb test > > > > I find that the newly created database "test" is not empty but still has > > all of the old relations and tables in it. How do I destroy a database > > so that it is truly gone!?!?!? I need a totally clean databaes to restore > > my data into. > > I'm going to put on my swami hat and read your mind now: > > Ah, your using Linux, specifically the Debian distribution, are you not? > > The problem is that psql by default connects to a database named the same > as the unix username, except on Debian, where it defaults to connecting > to the template1 database, since this is guaranteed to always exist. The > primary purpose of template1 is to serve as a (surprise) template to > build new databases on. > > What does this mean? Well, if you put anything in template1 (like install > the pgpsql scipt language) it will, be default, be installed in all your > new dbs. > > Unfortunately, if you accidently create tables in template1 (on Debian, > just forgetting to put the db name in a psql command line will do it), > they'll end up getting copied into every new db after that. > > So, connect to template1, and delete everything you don't want from there. > > Ross (learned from experience, I did) > -- > Ross J. Reedstrom, Ph.D., <[EMAIL PROTECTED]> > NSBRI Research Scientist/Programmer > Computer and Information Technology Institute > Rice University, 6100 S. Main St., Houston, TX 77005
Re: [ADMIN] backup/restore
On Fri, Sep 08, 2000 at 04:13:20PM -0700, Lindell Alderman wrote: > I am trying to backup and restore a database, but when run the following: > > dropdb test > createdb test > > I find that the newly created database "test" is not empty but still has > all of the old relations and tables in it. How do I destroy a database > so that it is truly gone!?!?!? I need a totally clean databaes to restore > my data into. I'm going to put on my swami hat and read your mind now: Ah, your using Linux, specifically the Debian distribution, are you not? The problem is that psql by default connects to a database named the same as the unix username, except on Debian, where it defaults to connecting to the template1 database, since this is guaranteed to always exist. The primary purpose of template1 is to serve as a (surprise) template to build new databases on. What does this mean? Well, if you put anything in template1 (like install the pgpsql scipt language) it will, be default, be installed in all your new dbs. Unfortunately, if you accidently create tables in template1 (on Debian, just forgetting to put the db name in a psql command line will do it), they'll end up getting copied into every new db after that. So, connect to template1, and delete everything you don't want from there. Ross (learned from experience, I did) -- Ross J. Reedstrom, Ph.D., <[EMAIL PROTECTED]> NSBRI Research Scientist/Programmer Computer and Information Technology Institute Rice University, 6100 S. Main St., Houston, TX 77005
[ADMIN] backup/restore
I am trying to backup and restore a database, but when run the following: dropdb test createdb test I find that the newly created database "test" is not empty but still has all of the old relations and tables in it. How do I destroy a database so that it is truly gone!?!?!? I need a totally clean databaes to restore my data into. -L
[ADMIN] Backup/restore large objects?
What is the best way to backup and restore large objects in the database? I don't think dump/dumpall can handle large objects or certain column types like serial. I hope there is a good way to do it such that if I upgrade to a new version of postgresql, I will be able to restore everything. I understand that tar.gzing the data directory will not work if the difference between versions changes too much. Robert B. Easter [EMAIL PROTECTED] (please cc me here, thx).
