[ADMIN] Syncronizing Databases at different Remote Location
Hello I am new in the field of Database Management. Most of times i worked with Software development and Database Design. I need some advice regarding database management. I have three Database (DB) servers at different locations. suppose A, B and C as three servers. where as Z is a central server which have VPN to all other servers. I have to insert/update records to central server Z if there is any insert/update in any of the A,B and C server. Now I can write a JAVA code that when ever there is insertion/updation of record of A/B/C do same in central Server Z using JAVA sockets I can send records to central server where Socket server will listen and then insert or update records in DB. But I come to know that there is a concept called Replication. I studied it and found that it is used for load balancing. I dont need load balancing I only need to Syncronize only Two tables of all A,B,C servers to central server Z. Please suggest how to move forward.(Open source solutions ) -- Best Regards Shakeel Abbas
Re: [ADMIN] Syncronizing Databases at different Remote Location
Shakeel, this can be done using symmetricDS. cheers //Sandeep --- On Wed, 4/11/09, ABBAS SHAKEEL wrote: > From: ABBAS SHAKEEL > Subject: [ADMIN] Syncronizing Databases at different Remote Location > To: pgsql-admin@postgresql.org > Date: Wednesday, 4 November, 2009, 3:48 PM > HelloI am new in the field of Database > Management. Most of times i worked with Software development > and Database Design.I need some advice regarding > database management. > I have three Database (DB) servers at different > locations. suppose A, B and C as three servers. where as Z > is a central server which have VPN to all other > servers. > > I have to insert/update records to central > server Z if there is any insert/update in any of the A,B and > C server. > Now I can write a JAVA code that when ever there > is insertion/updation of record of A/B/C do same in central > Server Z using JAVA sockets I can send records to central > server where Socket server will listen and then insert or > update records in DB. > > But I come to know that there is a concept > called Replication. I studied it and found that > it is used for load balancing. I dont need load balancing > I only need to Syncronize only Two tables of all A,B,C > servers to central server Z. > > > Please suggest how to move forward.(Open source > solutions ) > > -- > Best Regards > Shakeel Abbas > > > > From cricket scores to your friends. Try the Yahoo! India Homepage! http://in.yahoo.com/trynew -- 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] Syncronizing Databases at different Remote Location
Thanks Sandeep I will have a look to it and will inform you about success or failure. On Wed, Nov 4, 2009 at 4:03 PM, sandeep kumar wrote: > Shakeel, this can be done using symmetricDS. > > cheers > //Sandeep > > > > > --- On Wed, 4/11/09, ABBAS SHAKEEL wrote: > > > From: ABBAS SHAKEEL > > Subject: [ADMIN] Syncronizing Databases at different Remote Location > > To: pgsql-admin@postgresql.org > > Date: Wednesday, 4 November, 2009, 3:48 PM > > HelloI am new in the field of Database > > Management. Most of times i worked with Software development > > and Database Design.I need some advice regarding > > database management. > > I have three Database (DB) servers at different > > locations. suppose A, B and C as three servers. where as Z > > is a central server which have VPN to all other > > servers. > > > > I have to insert/update records to central > > server Z if there is any insert/update in any of the A,B and > > C server. > > Now I can write a JAVA code that when ever there > > is insertion/updation of record of A/B/C do same in central > > Server Z using JAVA sockets I can send records to central > > server where Socket server will listen and then insert or > > update records in DB. > > > > But I come to know that there is a concept > > called Replication. I studied it and found that > > it is used for load balancing. I dont need load balancing > > I only need to Syncronize only Two tables of all A,B,C > > servers to central server Z. > > > > > > Please suggest how to move forward.(Open source > > solutions ) > > > > -- > > Best Regards > > Shakeel Abbas > > > > > > > > > > > From cricket scores to your friends. Try the Yahoo! India Homepage! > http://in.yahoo.com/trynew > -- Best Regards Shakeel Abbas
Re: [ADMIN] fighting ' in transaction'
> Vladimir Rusinov wrote: > > We are having a lot of ' in transaction' processes in > production > > database. This does not seems to be big problem now, since they are > not > > taking any big locks. But I'm aware that sometime it can become a This *is* a big problem since your application is not handling transactions correctly. For the database maybe you are not worried because you don't have explicit locks at this moment, but you *will* have problems caused by excessive use of available connections (and threads in the JVM) when you need to scale up. > > problem. Currently I'm just killing all processes that are in 'idle > in > > transaction' for more than 1 minute. This is worst. This way you cause a forced ROLLBACK, does your application handle this? > > Our application is quite big and complex, so it's not easy to find > peace > > of code where it happens. How can I get transaction history for > > processes that idle in transaction to send it to developers so they > can > > find problem in code. If your application is big and complex it should handle transactions more carefully. What I did when I had a problem like yours in a big and complex high performance application was to find out the associated locks and show the affected tables and tuples to the developers. Check the entries in pg_locks related to the PID of the backend, a query like this can help you in 8.3: SELECT pg_locks.relation,page,tuple FROM pg_locks, pg_stat_activity WHERE pg_stat_activity.procpid=pg_locks.pid AND pg_stat_activity.current_query=' in transaction' ; In the beggining developers said "the application is big and complex to find it out so the problem is in PostgreSQL". It was the worst excuse I heard from developers. In the end they fixed the problem since thousands of connections were never enough for their application. > > We are running postgresql 8.3 with query logging off (turning it on > > > causes performance disaster). Application is run under jboss and > AFAIK > > uses hibernate. So you have a high performance environment. Good. Fix it on your application asap. Ask your developers about nested transactions. This is terrible for performance and cause the errors you're talking about. Flavio Henrique A. Gurgel Consultor -- 4Linux tel. 55-11-2125.4765 fax. 55-11-2125.4777 www.4linux.com.br -- 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] Syncronizing Databases at different Remote Location
Hello, I guess you a bit lucky here. I worked on exactly the same problem for a requirement in my project. And i've winded it up just today. I'm using Java for my application, but i'm doing this particular task outside Java, using shell directly. (It is a 3-line shell script) And then call this script from the Java appl. This is what i've done: psql (the postgres client) can connect to a remote host (with -h option) provided the server is configured to receive remote connections with appropriate authentication. (Please refer to my yesterday's mails in the same mailing list with the subject line "Need help in enabling remote connection") I maintain a table called 'Modifications' in each of the sites A,B,C. This table contains all changes (inserts, updates, deletes) i've done to all tables and are simply recorded as dml statements in this table. During synchronization (which is hopefully initialized at one of A,B,C), i copy the contents of this table into a text file and empty this table. Then with psql i connect to the remote database (Z) and simply run this dml file on it (using the cmd \i filename ), so that all the changes are reflected at Z. This method prevents redundant updates, conflicts and is even optimized. Since i worked on this problem for quite some time and didnt find any direct solution, i guess there isn't much else to do here. Cheers, Shruthi On Wed, Nov 4, 2009 at 3:48 PM, ABBAS SHAKEEL wrote: > Hello > I am new in the field of Database Management. Most of times i worked with > Software development and Database Design. > I need some advice regarding database management. > > I have three Database (DB) servers at different locations. suppose A, B and > C as three servers. where as Z is a central server which have VPN to all > other servers. > > I have to insert/update records to central server Z if there is any > insert/update in any of the A,B and C server. > > Now I can write a JAVA code that when ever there is insertion/updation of > record of A/B/C do same in central Server Z using JAVA sockets I can send > records to central server where Socket server will listen and then insert or > update records in DB. > > But I come to know that there is a concept called Replication. > I studied it and found that it is used for load balancing. I dont need > load balancing I only need to Syncronize only Two tables of all A,B,C > servers to central server Z. > > > Please suggest how to move forward.(Open source solutions ) > > > -- > Best Regards > Shakeel Abbas > >
[ADMIN] max_fsm_relations
Is there a way to find out "which of the 1000 relations" that msx_fsm will be kept track for. (I am using the default..and vacuumdb suggested increasing max_fsm_relations) -- 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] Syncronizing Databases at different Remote Location
Thanks Shurti That looks cool I will inform you also shortly about success or success :) On Wed, Nov 4, 2009 at 8:03 PM, Shruthi A wrote: > Hello, > > I guess you a bit lucky here. I worked on exactly the same problem for a > requirement in my project. And i've winded it up just today. I'm using > Java for my application, but i'm doing this particular task outside Java, > using shell directly. (It is a 3-line shell script) And then call this > script from the Java appl. > > This is what i've done: psql (the postgres client) can connect to a remote > host (with -h option) provided the server is configured to receive remote > connections with appropriate authentication. (Please refer to my > yesterday's mails in the same mailing list with the subject line "Need help > in enabling remote connection") I maintain a table called 'Modifications' > in each of the sites A,B,C. This table contains all changes (inserts, > updates, deletes) i've done to all tables and are simply recorded as dml > statements in this table. During synchronization (which is hopefully > initialized at one of A,B,C), i copy the contents of this table into a text > file and empty this table. Then with psql i connect to the remote database > (Z) and simply run this dml file on it (using the cmd \i filename ), so > that all the changes are reflected at Z. This method prevents redundant > updates, conflicts and is even optimized. > > Since i worked on this problem for quite some time and didnt find any > direct solution, i guess there isn't much else to do here. > > Cheers, > Shruthi > > > > On Wed, Nov 4, 2009 at 3:48 PM, ABBAS SHAKEEL > wrote: > >> Hello >> I am new in the field of Database Management. Most of times i worked with >> Software development and Database Design. >> I need some advice regarding database management. >> >> I have three Database (DB) servers at different locations. suppose A, B >> and C as three servers. where as Z is a central server which have VPN to all >> other servers. >> >> I have to insert/update records to central server Z if there is any >> insert/update in any of the A,B and C server. >> >> Now I can write a JAVA code that when ever there is insertion/updation of >> record of A/B/C do same in central Server Z using JAVA sockets I can send >> records to central server where Socket server will listen and then insert or >> update records in DB. >> >> But I come to know that there is a concept called Replication. >> I studied it and found that it is used for load balancing. I dont need >> load balancing I only need to Syncronize only Two tables of all A,B,C >> servers to central server Z. >> >> >> Please suggest how to move forward.(Open source solutions ) >> >> >> -- >> Best Regards >> Shakeel Abbas >> >> > -- Best Regards Shakeel Abbas
[ADMIN] overwrite permissions on default tablespace
Hi PGadmin list, I would like to know how can I overwrite the existing permission( on a default table space) defined in postgresql. cfg with :"set default_tablespace =x" for a user, trying : "revoke all on tablespace x from user" doesn't seems to help , the session level "set default ..." is not to 'satisfactory' approach for us. Thank you Isabella -- --- Isabella A. Ghiurea isabella.ghiu...@nrc-cnrc.gc.ca Canadian Astronomy Data Centre |http://www.nrc-cnrc.gc.ca/eng/services/hia/data-centre.html National Research Council of Canada, Herzberg Institute of Astrophysics 5071 West Saanich Road, Victoria BC V9E 2E7, Canada Phone: 250 363-3446 fax: 250 363-0045 -- 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] pg_autovacuum entries
If I were to use autovacuum on 8.1 , and If I specify the "ignore' tables in pg_autovacuum, 1. will the XID wraparound counter be reset after autovacuum finishes 2. If some of the ignore tables are never dropped or deleted in the future...how do the transaction semantics work with the rows in these tables (this is not likely to happen..but I am curious to know how transaction semantics work with the data in these tables) On Sun, Nov 1, 2009 at 9:56 AM, Alvaro Herrera wrote: > Anj Adu escribió: >> My goal is to reset the XID wraparound counter by performing vacuumdb >> (autovacuum is not an option for my for various reasons) > > Note that if you are on 8.1 (I can't recall) you need to vacuum the > whole database in a single command for Xid wraparound. Only on 8.2 and > higher you can get away with doing it one table at a time. > > -- > Alvaro Herrera http://www.CommandPrompt.com/ > The PostgreSQL Company - Command Prompt, Inc. > > -- > Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-admin > -- 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] pg_autovacuum entries
Anj Adu escribió: > If I were to use autovacuum on 8.1 , and If I specify the "ignore' > tables in pg_autovacuum, > > 1. will the XID wraparound counter be reset after autovacuum finishes No. I think you really need to get off 8.1. -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. -- 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] pg_autovacuum entries
Thanks..i will stick to vacuumdb in 8.1 (which works fine..just takes a couple of days to finish) Migration is an option..but our databases are enormous..(> 800G) and we have several of them On Wed, Nov 4, 2009 at 12:00 PM, Alvaro Herrera wrote: > Anj Adu escribió: >> If I were to use autovacuum on 8.1 , and If I specify the "ignore' >> tables in pg_autovacuum, >> >> 1. will the XID wraparound counter be reset after autovacuum finishes > > No. > > I think you really need to get off 8.1. > > -- > Alvaro Herrera http://www.CommandPrompt.com/ > The PostgreSQL Company - Command Prompt, Inc. > -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
