replication?

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:

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:

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