Re: [GENERAL] Does anyone user pg-pool II on real production ? Please help me.
Hi, > Hello everybody > > Does anyone user pg-pool II on real production ? Yes, I have customers who are using pgpool-II. > How many slave do you have? and how many size of database ? Mostly one slave, some of them use more than one, up to four or so. I am not sure how big their databases are though. > I need config my old retail system to support ~ 1500 user with 1000GB over > 4 years. I want to try pgpool-II but don't found real system use it. Here it is: http://www.sraoss.co.jp/case_study/daiichihoki_en.php Also the most recent case study of "Gengo" (http://gengo.com/) will be available soon in English. They are using PostgreSQL and pgpool-II on AWS. (Japanese case study text is already availble. Please take a look at if you like: http://www.sraoss.co.jp/case_study/gengo.php). > My system use direct SQL SELECT query and a lot pg procedure. Can pgpool-II > support load balance from SELECT my_procedure() ... Yes. pgpool-II can have a list of load-balancing-possible functions (procedures). > Please help me > > Thanks in advance. Sorry for my English. There is a pgpool-II specific mailing list. I suggest you to move to it. http://www.pgpool.net/mailman/listinfo/pgpool-general Best regards, -- Tatsuo Ishii SRA OSS, Inc. Japan English: http://www.sraoss.co.jp/index_en.php Japanese:http://www.sraoss.co.jp -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] postgresql versus riak for a global exchange
I’m familiar with both PostgreSQL and Riak (1.4, not 2.0). I know that Riak 2.0 now offers strong consistency. Have not yet seen what that does to performance. Big plusses for PostgreSQL: - you can do both relational and NOSQL tasks (the Binary JSON in the latest PostgreSQL). - well-tested consistency, ACID, etc. - lots of adapters and support. - big community Big plusses for Riak: - multi-master replication - multi-data center replication - easy to scale up We use PostgreSQL in combination with Riak for data storage (we have a tokenization service). We're currently using the EnterpriseDB multi-master PostgreSQL replication and are quite happy with it. The replication runs periodically, not streaming, so there is at least a 1 second delay for replication to occur. Riak replicates quicker — but then you don’t have the strong relational structure on top. As mentioned earlier, ‘exchange…trade…asset’ is a bit vague. In addition to just storing things, you’ll need to keep track of all sorts of log-in and contact info — perhaps not ideal for Riak. Probably best to consider precisely what traits your planned application has and then look to match against the database storage. May even end up with a mix of the two just as we have. Your decision may also depend on which development language/framework you chose for the implementation. —Ray > On Jan 5, 2015, at 11:37 AM, xu xiut wrote: > > Hello, I am looking at creating a toy project which may turn into an actual > business if I'm lucky, the ideal is generally just an exchange for people to > trade some type of asset. > > I'm looking at using either PostgreSQL or Riak, and I'm wondering if there > are opinions and suggestions that someone would be willing to share with me > when evaluating databases. > > This is the first time I've actually considered something besides PostgreSQL. > Riak 2.0 now offers strong consistency and I really respect the community and > the work that has gone into the project. It seems like it would be easy to > replicate across multiple data centers. > > Thanks for letting me ask this here! -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Hostnames, IDNs, Punycode and Unicode Case Folding
On Dec 29, 2014, at 5:36 PM, Mike Cardwell wrote: > So the system I've settled with is storing both the originally supplied > representation, *and* the lower cased punycode encoded version in a separate > column for indexing/search. This seems really hackish to me though. I actually do the same exact thing and don't think it's hackish. I actually really like being able to see the punycode next to the human representation on simple db pulls. It's barely more disk space and really useful. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] postgresql versus riak for a global exchange
On 1/5/2015 10:37 AM, xu xiut wrote: Hello, I am looking at creating a toy project which may turn into an actual business if I'm lucky, the ideal is generally just an exchange for people to trade some type of asset. I'm looking at using either PostgreSQL or Riak, and I'm wondering if there are opinions and suggestions that someone would be willing to share with me when evaluating databases. This is the first time I've actually considered something besides PostgreSQL. Riak 2.0 now offers strong consistency and I really respect the community and the work that has gone into the project. It seems like it would be easy to replicate across multiple data centers. Thanks for letting me ask this here! Never used Riak, no idea. BUT it would be awesome if you did it in both and could post a comparison. It would be neat to see plus/minus lists for the db's. Your question is pretty light on details: "Trade some type of asset". I'd be curious to see your layouts for both systems. Also the query types you think you'll need. (One spot Riak might have a problem is eventual consistency. If you only have 1 more foo, and two people post buy requests, will Riak sell more than one?) Riak seems to support distribution, replication, fail over, scale out, etc. In the sense that Riak is using those words, PG doesn't have all that stuff. -Andy -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] NODE
On 01/05/2015 11:27 AM, Ravi Kiran wrote: hi, I am going through the hashjoin algorithm in postgres. I find a function ExecHashjoin , which is called each time a new tuple is required by the hash join *Node.* * * could someone explain what exactly node mean in postgres. I believe a good place to start is the README: http://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f=src/backend/executor/README;h=8afa1e3e4a7596475cbf19a76c88d48a04aeef02;hb=HEAD Thanks -- Adrian Klaver adrian.kla...@aklaver.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] NODE
hi, I am going through the hashjoin algorithm in postgres. I find a function ExecHashjoin , which is called each time a new tuple is required by the hash join *Node.* could someone explain what exactly node mean in postgres. Thanks
Re: [GENERAL] Does anyone user pg-pool II on real production ? Please help me.
tuanhoanganh wrote: > Does anyone user pg-pool II on real production ? > How many slave do you have? and how many size of database ? a little bit, one client, some hundred GB. 2-3 years in production mode without problems. > > I need config my old retail system to support ~ 1500 user with 1000GB over 4 > years. I want to try pgpool-II but don't found real system use it. > > My system use direct SQL SELECT query and a lot pg procedure. Can pgpool-II > support load balance from SELECT my_procedure() ... Yes it can. But it doesn't know if your functions are read-only or write to the database - so you have to configure it (black/white - list) Read the docu - it's well ducumented. Andreas -- Really, I'm not out to destroy Microsoft. That will just be a completely unintentional side effect. (Linus Torvalds) "If I was god, I would recompile penguin with --enable-fly." (unknown) Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889° -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Does anyone user pg-pool II on real production ? Please help me.
Hello everybody Does anyone user pg-pool II on real production ? How many slave do you have? and how many size of database ? I need config my old retail system to support ~ 1500 user with 1000GB over 4 years. I want to try pgpool-II but don't found real system use it. My system use direct SQL SELECT query and a lot pg procedure. Can pgpool-II support load balance from SELECT my_procedure() ... Please help me Thanks in advance. Sorry for my English.
[GENERAL] ALTER TABLE to ADD BDR global sequence
While attempting to alter a table to add a global sequence as a primary key
using the following commands:
CREATE SEQUENCE my_table_id_seq USING bdr;
ALTER TABLE my_table
ADD COLUMN id integer PRIMARY KEY NOT NULL DEFAULT
nextval('my_table_id_seq'::regclass);
I started to notice some issues that concerned me. In order to create the
sequence, I had to have the replication running. To alter the table, I had
to stop replication. The only way I really knew how to do this was to remove
the bdr properties in the postgres configuration file and restart. At that
point, I executed the ALTER TABLE code, when it got to 15000 records,
Postgres informed me that I needed to turn replication back on so the nodes
could agree on additional sequence allocations. When I turned it back on, it
just kind-of wigged out.
So, how is this supposed to work? In addition, what happens when you have
very disparate databases that are both updated often and connected
occasionally (which is what we have). Will it quit doing inserts until it is
connected to the other databases again? That would be really bad.
Re: [GENERAL] bdr_init_copy fails when starting 2nd BDR node
I'm still experiencing similar problems. I'm not certain what parameter you are referring to when you say 'ehost'. Otherwise, I did want to clarify a couple of things. I have tried several combinations, each one fails in various ways. So ... (1) What is the exact syntax when calling bdr_init_copy from new nodes when your database name is not 'postgres' and your user name is not 'postgres'. Please note if you supply local or remote host/port in the command. (2) Should you do a pg_ctl start on new node before trying to execute bdr_init_copy. If I don't I get the error I posted earlier. I've attached the new nodes (dr) postgresql.conf file. -Original Message- From: 'Andres Freund' [mailto:and...@2ndquadrant.com] Sent: Wednesday, December 31, 2014 5:04 AM To: John Casey Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] bdr_init_copy fails when starting 2nd BDR node Hi, On 2014-12-30 21:12:17 -0500, John Casey wrote: > > > What was your bdr config at this point? The error message indicates > > that > it tries to > > connect to port 5432 on localhost - but the copy was taken from > 'main_node_ip'. > > Perhaps you forgot to specify the ehost in the config? > > # Here is my conf on the DR server (where I am running bdr_init_copy) > bdr.connections = 'primary' > bdr.primary_dsn = 'dbname=my_db host=primary_ip user=my_username port=5432' > bdr.primary_init_replica = on > bdr.primary_replica_local_dsn = 'dbname=my_db user=my_username port=5432' My guess is that this is the source of the problem - you probably have one system and one self compiled libpq around or something similar and they disagree about the location of the unix socket directory. It complains about: > > connections on Unix domain socket "/tmp/.s.PGSQL.5432"? which means given the above configuration it has to be primary_replica_local_dsn. Could you a) try to explicitly set unix_socket_directory=/tmp in postgresql.conf and host=/tmp in the above config? Also, please attach postgresql.conf. Greetings, Andres Freund -- Andres Freund http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services postgresql.conf Description: Binary data -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Loading Data Dumps for Tables Containing BDR Global Sequence
We have been trying to load our existing database that contains local sequences into a BDR-based database with global sequences. So, we update our schema in the new database to USING bdr for all sequences. We make certain all tables have a primary key. Then we dump our data from our existing database. We dump our existing database, including schema. We update the existing local sequences to USING bdr and remove the other initialization parameters. Then we must remove all setval's that occur after the COPY statements; because, BDR does not like them. We can load this data; but, the global sequences are all initialized to 1 on our primary database and 15000 on our DR database. We have went so far as to write a program that selects the max(id) on each table; then, it calls nextval until it reaches a value greater to or equal the max(id). This has to be run on all BDR nodes. Needless to say, this is slow and is a bad solution. There just doesn't seem to be a good way to accomplish this operation, at least not a documented way I have been able to find.
Re: [GENERAL] postgresql versus riak for a global exchange
On Mon, 5 Jan 2015 11:37:37 -0500 xu xiut wrote: > Hello, I am looking at creating a toy project which may turn into an actual > business if I'm lucky, the ideal is generally just an exchange for people > to trade some type of asset. > > I'm looking at using either PostgreSQL or Riak, and I'm wondering if there > are opinions and suggestions that someone would be willing to share with me > when evaluating databases. > > This is the first time I've actually considered something besides > PostgreSQL. Riak 2.0 now offers strong consistency and I really respect the > community and the work that has gone into the project. It seems like it > would be easy to replicate across multiple data centers. The big difference between an RDBMS like Postgres and a document store like Riak is data consistency. I'm talking about something completely different than the "strong consistency" promise they are now making. It's very easy to explain: Can you make foriegn keys in Riak? Check constraints? Data type constraints (such as ensuring that a particular value is a valid date)? PostgreSQL allows you to do all of these, and a properly designed RDBMS will ensure that your data is always valid ... i.e. no orphaned child records, or invalid dates, or negative numbers where there shouldn't be, etc. With Riak (or any other document store system I'm familiar with) the onus is on the programmers to ensure this. It's certainly possible to write data validation in the program logic to keep the data clean, but it's a lot more work than doing it in the database. Furthermore, in my experience, software developers don't do a good job of it -- but that's dependent on your software team and the QA practices you enforce. Summary: if data quality is important, PostgreSQL is probably the right choice. If you are OK with some poor quality data, and really need the features of Riak, then go that route. -- Bill Moran I need your help to succeed: http://gamesbybill.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] postgresql versus riak for a global exchange
Hello, I am looking at creating a toy project which may turn into an actual business if I'm lucky, the ideal is generally just an exchange for people to trade some type of asset. I'm looking at using either PostgreSQL or Riak, and I'm wondering if there are opinions and suggestions that someone would be willing to share with me when evaluating databases. This is the first time I've actually considered something besides PostgreSQL. Riak 2.0 now offers strong consistency and I really respect the community and the work that has gone into the project. It seems like it would be easy to replicate across multiple data centers. Thanks for letting me ask this here!
[GENERAL] declare cursor with hold+fetch count vs PQsendQuery+PQsetSingleRowMode - resource consumption and the efficiency
Hello! I would like to use "pagination" in my intranet client app. My aims: - allow user to open and scroll large lists from query (without narrowing the query) - at the same time minimize time until the showing of large lists to the user - and at the same time minimize backend-frontend traffic as possible (not loading all at once) User opens potentially large list of some documents and visually scrolls through the list opening some another related lists (master-detail queries). All those lists stays opened and should be scrollable until user closes them. Number of opened lists per each session is about <= 20. Basically there is two possibilities: 1. Client app explicitly open cursor and fetch records on demand. Cursors should remain open until the user closes corresponding list of documents - from minutes to hours. 2. Client app use PQsendQuery with PQsetSingleRowMode and PQgetResult insteed of PQExec. This variant forces to use one connection to backend per each opened list (each PQsendQuery). So insteed of one connection it would be dozens of. And questions are: 1. How much and what resources (cpu/mem/processes) "eats" each cursor/connection on backend? 2. From what resource usage of cursor/connection depends? 3. What would be more efficient in terms of resource usage and maybe speed? -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Replication: How to query current segments allocation relative to "Wal keep segments"?
On 05-01-2015 10:02, Michael Paquier wrote: On Mon, Jan 5, 2015 at 6:51 PM, Edson Carlos Ericksson Richter wrote: Would this kind of count being recorded somewhere else? How does the server knows that the wal_segments have been exhausted? You should evaluate the amount of wal_keep_segments necessary using the replication lag in terms of WAL position differences (LSN) between the master and its slaves. pg_stat_replication gives you the WAL position (LSN) up to where each slave has received WAL information. Combine it with pg_current_xlog_location() to determine what is the current location master is writing WAL and you can evaluate the number of WAL files that need to be retained on master. Knowing that each WAL file is normally 16MB, simply use pg_xlog_location_diff to calculate the WAL lag as a difference of bytes (for 9.4 a simple difference operation is possible with the data type pg_lsn), and then guess from it the number of WAL files that are actually necessary. If you care that much about WAL retention btw, consider using replication slots with 9.4, just be careful to monitor the partition where pg_xlog sits in. Despite being a completely valid statement, I've two contrary thoughts about it: 1) I cannot migrate production servers at my free will. It requires long planning, and probably will happen only in one or two years from now (year end 2015 or 2016) 2) I do prefer to monitor how much wal segments I'm really using (and in need), and then fix them up to 25% above this limit, than giving a chance to blow my disk space (I mean, is preferable to stop replication than put in risk whole database because of disk space) Thanks, Edson -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Check that streaming replica received all data after master shutdown
Hi all. I have a simple script for planned switchover of PostgreSQL (9.3 and 9.4) master to one of its replicas. This script checks a lot of things before doing it and one of them is that all data from master has been received by replica that is going to be promoted. Right now the check is done like below: On the master: postgres@pgtest03d ~ $ psql -t -A -c 'select pg_current_xlog_location();' 0/3390 postgres@pgtest03d ~ $ /usr/pgsql-9.3/bin/pg_ctl stop -m fast waiting for server to shut down done server stopped postgres@pgtest03d ~ $ /usr/pgsql-9.3/bin/pg_controldata | head pg_control version number:937 Catalog version number: 201306121 Database system identifier: 6061800518091528182 Database cluster state: shut down pg_control last modified: Mon 05 Jan 2015 06:47:57 PM MSK Latest checkpoint location: 0/3428 Prior checkpoint location:0/3328 Latest checkpoint's REDO location:0/3428 Latest checkpoint's REDO WAL file:001B0034 Latest checkpoint's TimeLineID: 27 postgres@pgtest03d ~ $ On the replica (after shutdown of master): postgres@pgtest03g ~ $ psql -t -A -c "select pg_xlog_location_diff(pg_last_xlog_replay_location(), '0/3428');" 104 postgres@pgtest03g ~ $ These 104 bytes seems to be the size of shutdown checkpoint record (as I can understand from pg_xlogdump output). postgres@pgtest03g ~/9.3/data/pg_xlog $ /usr/pgsql-9.3/bin/pg_xlogdump -s 0/3390 -t 27 rmgr: XLOGlen (rec/tot): 0/32, tx: 0, lsn: 0/3390, prev 0/3328, bkp: , desc: xlog switch rmgr: XLOGlen (rec/tot): 72/ 104, tx: 0, lsn: 0/3428, prev 0/3390, bkp: , desc: checkpoint: redo 0/3428; tli 27; prev tli 27; fpw true; xid 0/6010; oid 54128; multi 1; offset 0; oldest xid 1799 in DB 1; oldest multi 1 in DB 1; oldest running xid 0; shutdown pg_xlogdump: FATAL: error in WAL record at 0/3428: record with zero length at 0/3490 postgres@pgtest03g ~/9.3/data/pg_xlog $ I’m not sure that these 104 bytes will always be 104 bytes to have a strict equality while checking. Could it change in the future? Or is there a better way to understand that streaming replica received all data after master shutdown? The check that pg_xlog_location_diff returns 104 bytes seems a bit strange. Thanks. -- May the force be with you... http://simply.name
Re: [GENERAL] Replication: How to query current segments allocation relative to "Wal keep segments"?
On Mon, Jan 5, 2015 at 6:51 PM, Edson Carlos Ericksson Richter wrote: > Would this kind of count being recorded somewhere else? > How does the server knows that the wal_segments have been exhausted? You should evaluate the amount of wal_keep_segments necessary using the replication lag in terms of WAL position differences (LSN) between the master and its slaves. pg_stat_replication gives you the WAL position (LSN) up to where each slave has received WAL information. Combine it with pg_current_xlog_location() to determine what is the current location master is writing WAL and you can evaluate the number of WAL files that need to be retained on master. Knowing that each WAL file is normally 16MB, simply use pg_xlog_location_diff to calculate the WAL lag as a difference of bytes (for 9.4 a simple difference operation is possible with the data type pg_lsn), and then guess from it the number of WAL files that are actually necessary. If you care that much about WAL retention btw, consider using replication slots with 9.4, just be careful to monitor the partition where pg_xlog sits in. -- Michael -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Replication: How to query current segments allocation relative to "Wal keep segments"?
On 05-01-2015 02:08, Michael Paquier wrote: On Sun, Jan 4, 2015 at 1:48 AM, Edson Carlos Ericksson Richter wrote: How to query current segments allocation relative to "Wal keep segments" in each master server? What is your server version? You can have a look at pg_stat_replication on the master which contains information about the WAL segments written, flushed and replayed on each slave: http://www.postgresql.org/docs/devel/static/monitoring-stats.html#PG-STAT-REPLICATION-VIEW In production servers, 9.3.5. In development servers, 9.4.0. I had a quick look at this view before: Example (on 9.3.5): postgres=# select * from pg_stat_replication; pid | usesysid | usename | application_name | client_addr | client_hostname | client_port |backend_start | state | sent_location | write_location | flush_location | replay_location | sync_priority | sync_state --+--+--+--+-+-+-+--+---+---+--- -++-+---+ 2808 | 10 | postgres | walreceiver | 10.68.73.1 | | 36075 | 2015-01-03 20:17:48.53706-02 | streaming | 22/F94D1A90 | 22/F94D1A90 | 22/F94D1A90| 22/F94D1A90 | 0 | async (1 registro) but in either case (9.3.5, 9.4.0), I get lots of info, but not the count of wal_segments consumed. Would this kind of count being recorded somewhere else? How does the server knows that the wal_segments have been exhausted? Thanks, Edson -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
