Thread: [part 2] How to backup a postgreSQL of 80 GByte ?
Hallo again,
After addin a new table with two colums (and one row for each table)
to my Database I have changed my php-scripts to know, when a new table
is added or changed.
OK, now I have tried a full backup with pg-dump, which had killed my
Internt-Connection for days...
OK second try. I use the new index-table to determine which table has
changed...
Full-Backup whit extracting each table seperatly with:
pg_dump -F c -d -t _table_ _db_ > _table_.dmp.gz
Now I have around 830.000 tables *.dmp.gz on my backup-media.
If something is changed on my SQP-Server a cron-job run a script which
check the index-table and backup each new or changed table. - OK.
Now the big problem with:
pg_restore -d _new_db_ -F c _table_.dmp.gz
Hmmm, first I need to create a table and then I can insert the table ???
Does not work...
Help !!! - I am lost...
Creating a database and its use was easy, but backup is another problem
because it must be done from a ISDN-Link or something like this (same
speed = 128 kBit). Even if I use an E1, I can not use the 2 MBit because
I will have Client-Servers, Mail-Server, the Wave-Lan for my Clients and
other stuff running.
The other thing ist, what is, if I have the Master-SQL in Paris and a
Slave-SQL in Strasbourg ??? The Slave is automaticaly updatet if the
Master is changed...
OK, I need a identical system, maybe a little bit smaller CPU and less
memory for the Slave but...
1. How can I do a Master-Slave System ?
The Slave must be write only from outside (Paris)
2. What is the traffic for changements ?
Thanks for your help
Michelle
Michelle Konzack wrote: <snip> > The other thing ist, what is, if I have the Master-SQL in Paris and a > Slave-SQL in Strasbourg ??? The Slave is automaticaly updatet if the > Master is changed... > > OK, I need a identical system, maybe a little bit smaller CPU and less > memory for the Slave but... > > 1. How can I do a Master-Slave System ? > The Slave must be write only from outside (Paris) Hi Michelle, This sounds a lot like simple replication. There are a few PostgreSQL replication solutions in existence, with a summary of them available here: http://gborg.postgresql.org/genpage?replication_research > 2. What is the traffic for changements ? What do you mean? If you're wondering how much traffic will happen, it depends on which replication solution you use, and how many changes happen to your master database in Paris. Does this help? :-) Regards and best wishes, Justin Clift > Thanks for your help > Michelle > > > ---------------------------(end of broadcast)--------------------------- > TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org -- "My grandfather once told me that there are two kinds of people: those who work and those who take the credit. He told me to try to be in the first group; there was less competition there." - Indira Gandhi
On Wed, Jan 22, 2003 at 05:22:40AM +0200, Michelle Konzack wrote:
> OK, I need a identical system, maybe a little bit smaller CPU and less
> memory for the Slave but...
>
> 1. How can I do a Master-Slave System ?
> The Slave must be write only from outside (Paris)
We use PostgreSQL, Inc's eRServer for this. You just set things up
such that your ph_hba.conf doesn't allow other connections (that is,
no-one conencts except from the master). It still doesn't give you a
backup in the sense of "I want to go back to the database I had a day
ago, before that rogue got in and deleted everything."
You could probably _use_ the slave that way, however, by making the
slave tables actually views on something else, and using rules (and
probably triggers) to update another table. I have some ideas on how
to do some nifty stuff this way, but it hasn't been tested.
> 2. What is the traffic for changements ?
Just the SQL you need to send for each change. It's pretty light.
A
--
----
Andrew Sullivan 204-4141 Yonge Street
Liberty RMS Toronto, Ontario Canada
<andrew@libertyrms.info> M2P 2A8
+1 416 646 3304 x110