Thread: replication?
Does postgres support replication? I think this is the feature I need. I have a table in one db that I need to use from another db on another machine. The first db is behind two firewalls (for a good reason =) and the second needs to be hung out on the internet, so I can't really use the same db. But I would like to have the table on the internet Pg install to be updated whenever the master table behind the firewalls is updated. It doesn't need to be real-time, but maybe once every hour or half hour. I certainly have no problem cheating and just copying a file over or something if that will work. I looked through the docs but didn't see anything about replication in the Administrator manual, where I assume it'd be discussed. I did see a vague reference to a replication toolkit in the release notes, is this what I'm looking for? Thanks, Fran
http://www.greatbridge.org/genpage?replication_top All you could ever want to know about pgsql replication. God Luck! -r At 04:31 PM 7/18/01 -0400, Fran Fabrizio wrote: >Does postgres support replication? I think this is the feature I need. >I have a table in one db that I need to use from another db on another >machine. The first db is behind two firewalls (for a good reason =) and >the second needs to be hung out on the internet, so I can't really use >the same db. But I would like to have the table on the internet Pg >install to be updated whenever the master table behind the firewalls is >updated. It doesn't need to be real-time, but maybe once every hour or >half hour. I certainly have no problem cheating and just copying a file >over or something if that will work. I looked through the docs but >didn't see anything about replication in the Administrator manual, where >I assume it'd be discussed. I did see a vague reference to a >replication toolkit in the release notes, is this what I'm looking for? > >Thanks, >Fran > > >---------------------------(end of broadcast)--------------------------- >TIP 6: Have you searched our list archives? > >http://www.postgresql.org/search.mpl > > > >--- >Incoming mail is certified Virus Free. >Checked by AVG anti-virus system (http://www.grisoft.com). >Version: 6.0.251 / Virus Database: 124 - Release Date: 4/26/01 --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.251 / Virus Database: 124 - Release Date: 4/26/01
Hi, from: http://www.postgresql.org/idocs/index.php?backup.html : "pg_dump -h host1 dbname | psql -h host2 dbname" You could run this out of cron on your internal DB server (host1) And use a firewall rule like: # Allow internal DB server to use TCP for DB dumps pass in quick proto tcp from ip_of_host1 to ip_of_host2 port = 5432 flags S/FSRPAU keep state The rule above is for IP Filter http://coombs.anu.edu.au/~avalon/ Adam At 04:31 PM 7/18/01 -0400, Fran Fabrizio wrote: Does postgres support replication? I think this is the feature I need. I have a table in one db that I need to use from another db on another machine. The first db is behind two firewalls (for a good reason =) and the second needs to be hung out on the internet, so I can't really use the same db. But I would like to have the table on the internet Pg install to be updated whenever the master table behind the firewalls is updated. It doesn't need to be real-time, but maybe once every hour or half hour. I certainly have no problem cheating and just copying a file over or something if that will work. I looked through the docs but didn't see anything about replication in the Administrator manual, where I assume it'd be discussed. I did see a vague reference to a replication toolkit in the release notes, is this what I'm looking for? Thanks, Fran
Hi Fran, You might want to check out the PostgreSQL replication solutions linked to from : http://techdocs.postgresql.org/oresources.php#replication PostgreSQL Replicator seems to be the most complete, but I'm not sure (haven't yet gotten around to testing them properly). :-) Regards and best wishes, Justin Clift Fran Fabrizio wrote: > > Does postgres support replication? I think this is the feature I need. > I have a table in one db that I need to use from another db on another > machine. The first db is behind two firewalls (for a good reason =) and > the second needs to be hung out on the internet, so I can't really use > the same db. But I would like to have the table on the internet Pg > install to be updated whenever the master table behind the firewalls is > updated. It doesn't need to be real-time, but maybe once every hour or > half hour. I certainly have no problem cheating and just copying a file > over or something if that will work. I looked through the docs but > didn't see anything about replication in the Administrator manual, where > I assume it'd be discussed. I did see a vague reference to a > replication toolkit in the release notes, is this what I'm looking for? > > Thanks, > Fran > > ---------------------------(end of broadcast)--------------------------- > TIP 6: Have you searched our list archives? > > http://www.postgresql.org/search.mpl
hi,
i observed strange behavior using hash index. is it a known bug?
the index was created before loading data into table. when i recreate the
tc01_tc01pk____idx index everything is ok.
and i can see some other strange things with hash indexes in other tables.
is it possible that i'm using hash indexes in a bad way? or is it because
of using multiple indexes on one column?
any ideas?
thanks,
kuba
db_c01=# select version();
version
---------------------------------------------------------------
PostgreSQL 7.1.2 on i686-pc-linux-gnu, compiled by GCC 2.95.4
-- running debian, kernel 2.4, installed from .deb package
db_c01=# \d tc01
Table "tc01"
Attribute | Type | Modifier
-----------+-----------------------+--------------------------------------------------------
tc01pk___ | integer | not null default
nextval('"tc01_tc01pk____seq"'::text)
tc01mobil | character varying(30) | not null
tc01jmeno | character varying(50) |
tc01prijm | character varying(50) |
tc01zusta | double precision | not null
tc03pk___ | integer | not null
tc01akt__ | boolean | not null default 't'::bool
Indices: tc01_pkey,
tc01_tc01pk____idx,
tc01_tc01prijm_idx
-- tc01_tc01pk is a hash index on tc01pk___
-- tc01_pkey is a btree index on tc01pk___
db_c01=# explain select oid, * from tc01 order by tc01pk___ desc LIMIT 1;
Limit (cost=0.00..0.06 rows=1 width=57)
-> Index Scan Backward using tc01_pkey on tc01 (cost=0.00..59.00
rows=1000 width=57)
db_c01=# select oid, * from tc01 order by tc01pk___ desc LIMIT 1;
oid | tc01pk___ | tc01mobil | tc01jmeno | tc01prijm | tc01zusta |
tc03pk__
---------+-----------+------------+-----------+-----------+-----------+---------
1469963 | 10003 | 0609123456 | pepa | vomacka | 10000 |
(1 row)
db_c01=# explain select oid, * from tc01 where tc01pk___ = 10003;
Index Scan using tc01_tc01pk____idx on tc01 (cost=0.00..8.14 rows=10
width=57)
db_c01=# select oid, * from tc01 where tc01pk___ = 10003;
oid | tc01pk___ | tc01mobil | tc01jmeno | tc01prijm | tc01zusta |
tc03pk___ | t
-----+-----------+-----------+-----------+-----------+-----------+-----------+--
(0 rows)
Jakub Ouhrabka <jouh8664@ss1000.ms.mff.cuni.cz> writes: > and i can see some other strange things with hash indexes in other tables. > is it possible that i'm using hash indexes in a bad way? or is it because > of using multiple indexes on one column? It's my understanding that the hash index code isn't maintained and probably shouldn't be used. -Doug -- The rain man gave me two cures; he said jump right in, The first was Texas medicine--the second was just railroad gin, And like a fool I mixed them, and it strangled up my mind, Now people just get uglier, and I got no sense of time... --Dylan
Jakub Ouhrabka <jouh8664@ss1000.ms.mff.cuni.cz> writes:
> i observed strange behavior using hash index. is it a known bug?
No, not a *known* bug. Your message does not give sufficient info to
try to reproduce the problem, however. Could you try to come up with
a self-contained example?
regards, tom lane
hi,
i have table1 and table2. in table1 there is a foreign key referencing id
in table2. i'd like to do something like this:
CREATE FUNCTION foo() RETURNS INTEGER AS '
DECLARE
BEGIN
UPDATE table1 SET not_key = 100;
DELETE FROM table1;
DELETE FROM table2;
RETURN 0;
END;
' LANGUAGE 'plpgsql';
when i run select foo(); i always get:
ERROR: <unnamed> referential integrity violation - key referenced from
table1 not found in table2
(i think that's because constraints are checked at the end of statement
and this function is treated as one statement... or am i wrong?)
when i try it in psql as separate commands in one transaction it's ok:
BEGIN;
UPDATE table1 SET not_key = 100;
DELETE FROM table1;
DELETE FROM table2;
COMMIT;
is there any way to do something like this in plpgsql function?
thanks in advance,
kuba