pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedbackup question
4+ messages / 4 participants
[nested] [flat]
* backup question
@ 2009-03-31 23:27 Kasia Tuszynska <ktuszynska@esri.com>
0 siblings, 1 reply; 4+ messages in thread
From: Kasia Tuszynska @ 2009-03-31 23:27 UTC (permalink / raw)
To: pgsql-admin
Hello Postgres Gurus,
I have a restore problem.
If you do the backup as a text file:
pg_dump.exe -i -h machine -p 5432 -U postgres -F p -v -f "C:\dbname_text.dump.backup" dbname
You can see the order in which the restore will happen. And the restore seems to be happening in the following order
create objects (objects named in descending order of schema name.tablename), adam.table is created before public.table
populate objects (objects named in descending order of schema name.tablename), adam.table is populated before public.table
grant privs to objects (objects named in descending order of schema name.tablename) adam.table is granted privs to before public.table
The problem arises, if data in lets say the adam schema is dependent on tables in the public schema, since the data in the public schema does not exist yet, being created later.
If there was an option to do a backup and restore of a single schema, that would solve my problem.
Has anyone dealt with this issue before? Any workarounds?
Thank you,
Sincerely,
Kasia
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: backup question
@ 2009-03-31 23:34 Tom Lane <tgl@sss.pgh.pa.us>
parent: Kasia Tuszynska <ktuszynska@esri.com>
0 siblings, 0 replies; 4+ messages in thread
From: Tom Lane @ 2009-03-31 23:34 UTC (permalink / raw)
To: Kasia Tuszynska <ktuszynska@esri.com>; +Cc: pgsql-admin
Kasia Tuszynska <ktuszynska@esri.com> writes:
> The problem arises, if data in lets say the adam schema is dependent on tables in the public schema, since the data in the public schema does not exist yet, being created later.
That's not supposed to happen. Are you possibly running an early 8.3
release? pg_dump 8.3.0-5 had a problem with user-defined operator
classes that could cause misordering of the output. If it's not that,
we'll need a lot more information.
BTW, the -i switch is dangerous. Don't use it as a routine measure.
regards, tom lane
^ permalink raw reply [nested|flat] 4+ messages in thread
* Backup question
@ 2011-03-01 09:50 A B <gentosaker@gmail.com>
0 siblings, 1 reply; 4+ messages in thread
From: A B @ 2011-03-01 09:50 UTC (permalink / raw)
To: pgsql-admin
Hello.
In the docs of 8.4 I read that one way of doing filesystem backup of
PostgreSQL is to
1. run rsync
2. stop the server
3. run second rsync
4. start server
But what would happen if you
1. run rsync
2. throw server through the window and buy new server
3. copy the rsynced data
4. start server
now, what would happen?
I guess the server would think: uh-oh, it has crashed, I'll try to fix it.
I understand that you will loose data, but will it be able to get
database in shape and running?
And to reduce the amount of lost data, is there any clever "sync"
command I can run before step 1 to write more stuff to disk?
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Backup question
@ 2011-03-01 10:13 Vibhor Kumar <vibhor.aim@gmail.com>
parent: A B <gentosaker@gmail.com>
0 siblings, 0 replies; 4+ messages in thread
From: Vibhor Kumar @ 2011-03-01 10:13 UTC (permalink / raw)
To: A B <gentosaker@gmail.com>; +Cc: pgsql-admin
On Mar 1, 2011, at 3:20 PM, A B wrote:
>
> But what would happen if you
> 1. run rsync
> 2. throw server through the window and buy new server
> 3. copy the rsynced data
> 4. start server
> now, what would happen?
> I guess the server would think: uh-oh, it has crashed, I'll try to fix it.
This will give you Inconsisten Backup and PG will try to recover the database
> I understand that you will loose data, but will it be able to get
> database in shape and running?
May be Or may not be. I have seen scenario's where people had to use pg_resetxlog
> And to reduce the amount of lost data, is there any clever "sync"
> command I can run before step 1 to write more stuff to disk?
Have a look of Hot/Online Backup.
http://www.postgresql.org/docs/8.4/interactive/continuous-archiving.html
Thanks & Regards,
Vibhor
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2011-03-01 10:13 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2009-03-31 23:27 backup question Kasia Tuszynska <ktuszynska@esri.com>
2009-03-31 23:34 ` Tom Lane <tgl@sss.pgh.pa.us>
2011-03-01 09:50 Backup question A B <gentosaker@gmail.com>
2011-03-01 10:13 ` Re: Backup question Vibhor Kumar <vibhor.aim@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox