[GENERAL] BDR, 9.4 release schedule, PostgreSQL in Azure, pgPool-II
Hi All :) A few questions from a newbie. 1. Use someone BDR in production (https://wiki.postgresql.org/wiki/BDR_Project)? 2. When ~ is expected to release 9.4? 3. Use someone PostgreSQL in Azure and can give any advice? 4. Do I understand correctly that the closest analog of mysql-proxy - it's pgPool-II. The scheme is very simple - every frontend with the code works with own database's instance, and use another one instance of DB only in case of problems with main (own) DB. DB under fairly simple application. The desire to use BDR dictated by scaling in breadth on comfortable on the price / quality instances and not be fooled unreliability of individual VM in Azure (ie, at the minimum, we have two VMs with PostgreSQL in one availability set of Azure). Thanks in advance! P.S. Sorry for my bad English ))) -- Savchuk Taras
Re: [GENERAL] BDR, 9.4 release schedule, PostgreSQL in Azure, pgPool-II
Hi All :) A few questions from a newbie. 1. Use someone BDR in production (https://wiki.postgresql.org/wiki/BDR_Project)? 2. When ~ is expected to release 9.4? I don't think it's in 9.4. 3. Use someone PostgreSQL in Azure and can give any advice? I have no experience with Azure. So no idea. 4. Do I understand correctly that the closest analog of mysql-proxy - it's pgPool-II. The scheme is very simple - every frontend with the code works with own database's instance, and use another one instance of DB only in case of problems with main (own) DB. I have no experience with mysql-proxy. However quick googling suggests that mysql-proxy is resemble to pgpool-II except that pgpool-II has failover functionality, while mysql-proxy not. Best regards, -- Tatsuo Ishii SRA OSS, Inc. Japan English: http://www.sraoss.co.jp/index_en.php Japanese:http://www.sraoss.co.jp DB under fairly simple application. The desire to use BDR dictated by scaling in breadth on comfortable on the price / quality instances and not be fooled unreliability of individual VM in Azure (ie, at the minimum, we have two VMs with PostgreSQL in one availability set of Azure). Thanks in advance! P.S. Sorry for my bad English ))) -- Savchuk Taras -- 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] SSL Certificates in Postgres 9.3 and Windows 7
On 11/08/2014 08:07 PM, harpagornis wrote: I am trying to implement SSL certificates with postgres 9.3 locally in Windows 7. In Windows Component Services / Local Services, postrgres is configured to start automatically, with Log On as a local system account. Using my Windows administrator account, in a command prompt inside my data folder, when I execute postgres -D . , I get the message, Redirecting logging output to the logging collector service. I also get this error message in my log file: ? When I try to connect in PgAdminIII I get the error message, Server isn't listening What am I doing wrong? Right now, just for development purposes, do I need to have a root certificate? I tried unsuccessfully to create one with makecert but couldn't get the flags and options right. Would seem either Postgres has not started or is listening on an interface different from what you are trying to connect to. I followed the postgres openssl documentation for creating the privkey.pem, server.req, server.key and server.crt files, ie.: ? This is the entire pg_hba.conf file: ? Also, which of those last two lines in the pg_hba.conf file should I be using to require SSL certificates for all postgres accounts? Is it even possible to require a SSL certificate for the postgres account? This the entire postgresql.conf file: ? Thank you for all comments and suggestions. More comments/suggestions will have to wait until the missing pieces are filled in. -- 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
Re: [GENERAL] SSL Certificates in Postgres 9.3 and Windows 7
On 9 November 2014 05:07, harpagornis shenl...@runbox.com wrote: I am trying to implement SSL certificates with postgres 9.3 locally in Windows 7. In Windows Component Services / Local Services, postrgres is configured to start automatically, with Log On as a local system account. Postgres on Windows will not start if it's running on account which belongs to Administrators or PowerUsers groups. LocalSystem belongs to Administrators. On most cases you would like to use for example NetworkService account, however you need to setup proper permissions on cluster data directory (full access), postgres installation directory (read+execute mostly), and in some cases also for root drive of cluster data directory (read access). Best regards, Krystian Bigaj
Re: [GENERAL] SSL Certificates in Postgres 9.3 and Windows 7
Adrian Klaver-4 wrote Thank you for all comments and suggestions. More comments/suggestions will have to wait until the missing pieces are filled in. I read most of these mailing list emails via Nabble and the pieces you show as missing are present in what I am reading. If I go to reply and quote the original message the missing sections are sour rounded by raw tags. Looking at the official mailing list archive these sections are missing there. -- View this message in context: http://postgresql.1045698.n5.nabble.com/SSL-Certificates-in-Postgres-9-3-and-Windows-7-tp5826230p5826246.html Sent from the PostgreSQL - general mailing list archive at Nabble.com. -- 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] SSL Certificates in Postgres 9.3 and Windows 7
Quoting original message to try and show file contents... harpagornis wrote I am trying to implement SSL certificates with postgres 9.3 locally in Windows 7. In Windows Component Services / Local Services, postrgres is configured to start automatically, with Log On as a local system account. Using my Windows administrator account, in a command prompt inside my data folder, when I execute postgres -D . , I get the message, Redirecting logging output to the logging collector service. I also get this error message in my log file: 2014-11-09 03:05:13 GMT LOG: client certificates can only be checked if a root certificate store is available 2014-11-09 03:05:13 GMT HINT: Make sure the configuration parameter ssl_ca_file is set. 2014-11-09 03:05:13 GMT CONTEXT: line 2 of configuration file D:/PostgresDat/pg_hba.conf 2014-11-09 03:05:13 GMT FATAL: could not load pg_hba.conf When I try to connect in PgAdminIII I get the error message, Server isn't listening What am I doing wrong? Right now, just for development purposes, do I need to have a root certificate? I tried unsuccessfully to create one with makecert but couldn't get the flags and options right. I followed the postgres openssl documentation for creating the privkey.pem, server.req, server.key and server.crt files, ie.: 1. openssl genrsa –out privkey.pem 2048 2. openssl req -new -key privkey.pem -out server.req –config D:\openssl\v9.8\openssl.cnf” 3. openssl rsa -in privkey.pem -out server.key openssl req -x509 -in server.req -text -key server.key -out server.crt -config D:\openssl\v9.8\openssl.cnf” This is the entire pg_hba.conf file: # TYPE DATABASE USERADDRESSMETHOD hostssl all all 127.0.0.1/32 cert clientcert=1 hostssl postgres postgres ::1/128 trust #hostssl all all ::1/128cert clientcert=1 Also, which of those last two lines in the pg_hba.conf file should I be using to require SSL certificates for all postgres accounts? Is it even possible to require a SSL certificate for the postgres account? This the entire postgresql.conf file: listen_addresses = '*' port = 5432 # (change requires restart) max_connections = 100 # (change requires restart) # - Security and Authentication - ssl = on # (change requires restart) ssl_ciphers = 'DEFAULT:!LOW:!EXP:!MD5:@STRENGTH' # allowed SSL ciphers ssl_renegotiation_limit = 512MB # amount of data between renegotiations ssl_cert_file = 'server.crt' # (change requires restart) ssl_key_file = 'server.key' # (change requires restart) #ssl_ca_file = 'root.crt' password_encryption = on shared_buffers = 128MB# min 128kB # ERROR REPORTING AND LOGGING # - Where to Log - log_destination = 'stderr' # This is used when logging to stderr: logging_collector = on# Enable capturing of stderr and csvlog # into log files. Required to be on for # csvlogs. # (change requires restart) log_line_prefix = '%t ' # special values: # - Locale and Formatting - datestyle = 'iso, mdy' timezone = 'US/Central' lc_messages = 'English_United States.1252'# locale for system error message lc_monetary = 'English_United States.1252'# locale for monetary formatting lc_numeric = 'English_United States.1252' # locale for number formatting lc_time = 'English_United States.1252'# locale for time formatting # default configuration for text search default_text_search_config = 'pg_catalog.english' Thank you for all comments and suggestions. David J. -- View this message in context: http://postgresql.1045698.n5.nabble.com/SSL-Certificates-in-Postgres-9-3-and-Windows-7-tp5826230p5826247.html Sent from the PostgreSQL - general mailing list archive at Nabble.com. -- 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] SSL Certificates in Postgres 9.3 and Windows 7
As suggested, I changed the data folder permissions from Read Only to allow Read / Write (I was already logged in as Administrator), but the errors are the same. If I remove the SSL-related lines in pg_hba.conf and postgresql.conf, and use the following lines instead in pg_hba.conf, I am able to connect to the database using PgAdminIII: But, even then, after doing that, and setting ssl=off in postgresql.conf, when I run the command prompt and execute postgres -D . in the data folder, I get these errors in the command prompt console: With that, there are no entries in the postgres log file. -- View this message in context: http://postgresql.1045698.n5.nabble.com/SSL-Certificates-in-Postgres-9-3-and-Windows-7-tp5826230p5826249.html Sent from the PostgreSQL - general mailing list archive at Nabble.com. -- 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] SSL Certificates in Postgres 9.3 and Windows 7
Windows automatically changed the data folder attribute back to Read Only. The only Windows groups that have full permission are SYSTEM, Administrators and my administrator /user account. -- View this message in context: http://postgresql.1045698.n5.nabble.com/SSL-Certificates-in-Postgres-9-3-and-Windows-7-tp5826230p5826251.html Sent from the PostgreSQL - general mailing list archive at Nabble.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] UUID index unused
I am assuming I am crazy and missing something completely obvious but I cannot get postgres (9.3.5) to use an index on a UUID, ever. The main table has a natural composite key (2 uuids and a timestamp) with which it always uses the timestamp as the index condition and filters on the UUIDs. This occurs when when we do a query for a specific item comparing all 3 key columns with equality. Other tables that have a single UUID column index also fail to ever utilize any available indices; querying for a specific UUID always results in a table scan for them. Switching the UUID columns to text immediately solves the issues and index usage is as expected in all cases. After hours of fiddling I figured it was time to ask. Any ideas? P.S. Before it’s suggested… we have millions of independent devices generating the ids which drives our use of UUIDs. Any thoughts of replacing them only result in us building something that’s basically a UUID to replace it. -- 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] UUID index unused
On 11/09/2014 10:58 AM, Kevin Wooten wrote: I am assuming I am crazy and missing something completely obvious but I cannot get postgres (9.3.5) to use an index on a UUID, ever. The main table has a natural composite key (2 uuids and a timestamp) with which it always uses the timestamp as the index condition and filters on the UUIDs. This occurs when when we do a query for a specific item comparing all 3 key columns with equality. Other tables that have a single UUID column index also fail to ever utilize any available indices; querying for a specific UUID always results in a table scan for them. Switching the UUID columns to text immediately solves the issues and index usage is as expected in all cases. After hours of fiddling I figured it was time to ask. Any ideas? The only thing I could after a quick search was this: http://stackoverflow.com/questions/22720130/how-to-use-uuid-with-postgresql-gist-index-type Sort of a hybrid approach. P.S. Before it’s suggested… we have millions of independent devices generating the ids which drives our use of UUIDs. Any thoughts of replacing them only result in us building something that’s basically a UUID to replace it. -- 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
Re: [GENERAL] UUID index unused
Kevin Wooten kd...@me.com writes: I am assuming I am crazy and missing something completely obvious but I cannot get postgres (9.3.5) to use an index on a UUID, ever. Worksforme: regression=# create table foo (f1 uuid primary key); CREATE TABLE regression=# explain select * from foo where f1 = 'a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11'; QUERY PLAN -- Index Only Scan using foo_pkey on foo (cost=0.15..8.17 rows=1 width=16) Index Cond: (f1 = 'a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11'::uuid) (2 rows) The main table has a natural composite key (2 uuids and a timestamp) with which it always uses the timestamp as the index condition and filters on the UUIDs. This probably has little to do with the datatype as such, and much to do with the specifics of your query, the available indexes, and the table's statistics. It's hard to speculate further without lots more detail about those things. regards, tom lane -- 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] SSL Certificates in Postgres 9.3 and Windows 7
On 11/09/2014 10:14 AM, David G Johnston wrote: Adrian Klaver-4 wrote Thank you for all comments and suggestions. More comments/suggestions will have to wait until the missing pieces are filled in. I read most of these mailing list emails via Nabble and the pieces you show as missing are present in what I am reading. If I go to reply and quote the original message the missing sections are sour rounded by raw tags. Hmm, is there a way to make Nabble aware of this and fix it? Looking at the official mailing list archive these sections are missing there. -- 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
Re: [GENERAL] SSL Certificates in Postgres 9.3 and Windows 7
On 11/09/2014 10:17 AM, David G Johnston wrote: Quoting original message to try and show file contents... harpagornis wrote I am trying to implement SSL certificates with postgres 9.3 locally in Windows 7. In Windows Component Services / Local Services, postrgres is configured to start automatically, with Log On as a local system account. Using my Windows administrator account, in a command prompt inside my data folder, when I execute postgres -D . , I get the message, Redirecting logging output to the logging collector service. I also get this error message in my log file: 2014-11-09 03:05:13 GMT LOG: client certificates can only be checked if a root certificate store is available 2014-11-09 03:05:13 GMT HINT: Make sure the configuration parameter ssl_ca_file is set. 2014-11-09 03:05:13 GMT CONTEXT: line 2 of configuration file D:/PostgresDat/pg_hba.conf 2014-11-09 03:05:13 GMT FATAL: could not load pg_hba.conf When I try to connect in PgAdminIII I get the error message, Server isn't listening What am I doing wrong? Right now, just for development purposes, do I need to have a root certificate? I tried unsuccessfully to create one with makecert but couldn't get the flags and options right. My suggestion would be to read: http://www.postgresql.org/docs/9.3/interactive/ssl-tcp.html The short version: If you want a client to supply a certificate then you need a valid ssl_ca_file. If you do not want that to happen do not set cert clientcert=1 in pg_hba.conf David J. -- View this message in context: http://postgresql.1045698.n5.nabble.com/SSL-Certificates-in-Postgres-9-3-and-Windows-7-tp5826230p5826247.html Sent from the PostgreSQL - general mailing list archive at Nabble.com. -- 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
Re: [GENERAL] SSL Certificates in Postgres 9.3 and Windows 7
On Sunday, November 9, 2014, Adrian Klaver adrian.kla...@aklaver.com wrote: On 11/09/2014 10:14 AM, David G Johnston wrote: Adrian Klaver-4 wrote Thank you for all comments and suggestions. More comments/suggestions will have to wait until the missing pieces are filled in. I read most of these mailing list emails via Nabble and the pieces you show as missing are present in what I am reading. If I go to reply and quote the original message the missing sections are sour rounded by raw tags. Hmm, is there a way to make Nabble aware of this and fix it? Looking at the official mailing list archive these sections are missing there. -- Adrian Klaver adrian.kla...@aklaver.com I don't know how the OP sent the original e-mail but since I could read the problem areas the question is why other e-mail clients aren't seeing them...
Re: [GENERAL] SSL Certificates in Postgres 9.3 and Windows 7
On Sun, Nov 9, 2014 at 11:37 PM, David Johnston david.g.johns...@gmail.com wrote: On Sunday, November 9, 2014, Adrian Klaver adrian.kla...@aklaver.com wrote: On 11/09/2014 10:14 AM, David G Johnston wrote: Adrian Klaver-4 wrote Thank you for all comments and suggestions. More comments/suggestions will have to wait until the missing pieces are filled in. I read most of these mailing list emails via Nabble and the pieces you show as missing are present in what I am reading. If I go to reply and quote the original message the missing sections are sour rounded by raw tags. Hmm, is there a way to make Nabble aware of this and fix it? Looking at the official mailing list archive these sections are missing there. -- Adrian Klaver adrian.kla...@aklaver.com I don't know how the OP sent the original e-mail but since I could read the problem areas the question is why other e-mail clients aren't seeing them... I'd be more interested in how *you* could see them - unless you are just referring to seeing them on nabble.com? The original as delivered through the mailinglist is in it's raw form at http://www.postgresql.org/message-id/raw/1415506067738-5826230.p...@n5.nabble.com - which does not contain those parts. And it wasn't event sent as multipart, so there is not much of ways to misparse it. -- Magnus Hagander Me: http://www.hagander.net/ Work: http://www.redpill-linpro.com/ -- 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] UUID index unused
This affirmation that it indeed does work set me straight. I inadvertently made a previously immutable UUID function volatile; it was providing the UUIDs in the query. On Nov 9, 2014, at 2:12 PM, Tom Lane t...@sss.pgh.pa.us wrote: Kevin Wooten kd...@me.com writes: I am assuming I am crazy and missing something completely obvious but I cannot get postgres (9.3.5) to use an index on a UUID, ever. Worksforme: regression=# create table foo (f1 uuid primary key); CREATE TABLE regression=# explain select * from foo where f1 = 'a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11'; QUERY PLAN -- Index Only Scan using foo_pkey on foo (cost=0.15..8.17 rows=1 width=16) Index Cond: (f1 = 'a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11'::uuid) (2 rows) The main table has a natural composite key (2 uuids and a timestamp) with which it always uses the timestamp as the index condition and filters on the UUIDs. This probably has little to do with the datatype as such, and much to do with the specifics of your query, the available indexes, and the table's statistics. It's hard to speculate further without lots more detail about those things. regards, tom lane -- 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] SSL Certificates in Postgres 9.3 and Windows 7
Yes, that is what I was referring to. The Nabble.com website showed them. http://postgresql.1045698.n5.nabble.com/SSL-Certificates-in-Postgres-9-3-and-Windows-7-td5826230.html David J. On Sunday, November 9, 2014, Magnus Hagander mag...@hagander.net wrote: On Sun, Nov 9, 2014 at 11:37 PM, David Johnston david.g.johns...@gmail.com javascript:; wrote: On Sunday, November 9, 2014, Adrian Klaver adrian.kla...@aklaver.com javascript:; wrote: On 11/09/2014 10:14 AM, David G Johnston wrote: Adrian Klaver-4 wrote Thank you for all comments and suggestions. More comments/suggestions will have to wait until the missing pieces are filled in. I read most of these mailing list emails via Nabble and the pieces you show as missing are present in what I am reading. If I go to reply and quote the original message the missing sections are sour rounded by raw tags. Hmm, is there a way to make Nabble aware of this and fix it? Looking at the official mailing list archive these sections are missing there. -- Adrian Klaver adrian.kla...@aklaver.com javascript:; I don't know how the OP sent the original e-mail but since I could read the problem areas the question is why other e-mail clients aren't seeing them... I'd be more interested in how *you* could see them - unless you are just referring to seeing them on nabble.com? The original as delivered through the mailinglist is in it's raw form at http://www.postgresql.org/message-id/raw/1415506067738-5826230.p...@n5.nabble.com - which does not contain those parts. And it wasn't event sent as multipart, so there is not much of ways to misparse it. -- Magnus Hagander Me: http://www.hagander.net/ Work: http://www.redpill-linpro.com/
[GENERAL] Query Plans on Slaves
Hi all! We're working on our architecture for our next set of systems, and we normally have a simple master/slave with wal shipping and hot_standby set up, with pgpool sitting in front of both to do load balancing. However, one piece I'm very confused about is the query plans on the slave server. Let's pretend we have a master server with: (2) E5-2660 CPU's (4) S3700 400GB SSD's 256GB of RAM And multiple slave servers that looks like: (1) E3-1290 CPU's (2) Intel 520 SSD's 32GB of RAM There's a significant difference between the master and slaves. Does the ANALYZE command run on each system, and work differently on each system, or would the slave servers use the query plans from the master machine? Thanks! -- Anthony
Re: [GENERAL] Query Plans on Slaves
Hi all! We're working on our architecture for our next set of systems, and we normally have a simple master/slave with wal shipping and hot_standby set up, with pgpool sitting in front of both to do load balancing. However, one piece I'm very confused about is the query plans on the slave server. Let's pretend we have a master server with: (2) E5-2660 CPU's (4) S3700 400GB SSD's 256GB of RAM And multiple slave servers that looks like: (1) E3-1290 CPU's (2) Intel 520 SSD's 32GB of RAM There's a significant difference between the master and slaves. Can you show us the explain analyze result on both master and slave? Does the ANALYZE command run on each system, and work differently on each system, or would the slave servers use the query plans from the master machine? ANALYZE can only be executed on master. Slave creates its own plan. However since statistics should be same as master, I guess if you get different plan, then postgresql.conf maybe different among master and slave. 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] Query Plans on Slaves
We're still considering this architecture - so I don't have any plans. My question is, does the ANALYZE command, which is only executed on the master, mean that the statistics / plans that are used on the master are ALSO used on the slaves? OR does the slave create it's own plan? I would anticipate very different plans between the two machines, and different postgresql.conf settings as well. I would *want* different query plans between the two. Is that possible? On Sun, Nov 9, 2014 at 8:50 PM, Tatsuo Ishii is...@postgresql.org wrote: Hi all! We're working on our architecture for our next set of systems, and we normally have a simple master/slave with wal shipping and hot_standby set up, with pgpool sitting in front of both to do load balancing. However, one piece I'm very confused about is the query plans on the slave server. Let's pretend we have a master server with: (2) E5-2660 CPU's (4) S3700 400GB SSD's 256GB of RAM And multiple slave servers that looks like: (1) E3-1290 CPU's (2) Intel 520 SSD's 32GB of RAM There's a significant difference between the master and slaves. Can you show us the explain analyze result on both master and slave? Does the ANALYZE command run on each system, and work differently on each system, or would the slave servers use the query plans from the master machine? ANALYZE can only be executed on master. Slave creates its own plan. However since statistics should be same as master, I guess if you get different plan, then postgresql.conf maybe different among master and slave. Best regards, -- Tatsuo Ishii SRA OSS, Inc. Japan English: http://www.sraoss.co.jp/index_en.php Japanese:http://www.sraoss.co.jp
Re: [GENERAL] Query Plans on Slaves
We're still considering this architecture - so I don't have any plans. My question is, does the ANALYZE command, which is only executed on the master, mean that the statistics / plans that are used on the master are ALSO used on the slaves? OR does the slave create it's own plan? The statistics is same on master and slave. Plans are made on master and slave independently. I would anticipate very different plans between the two machines, and different postgresql.conf settings as well. I would *want* different query plans between the two. Is that possible? If you have different postgresql.conf settings on slave, you could get different plans on slave. Best regards, -- Tatsuo Ishii SRA OSS, Inc. Japan English: http://www.sraoss.co.jp/index_en.php Japanese:http://www.sraoss.co.jp On Sun, Nov 9, 2014 at 8:50 PM, Tatsuo Ishii is...@postgresql.org wrote: Hi all! We're working on our architecture for our next set of systems, and we normally have a simple master/slave with wal shipping and hot_standby set up, with pgpool sitting in front of both to do load balancing. However, one piece I'm very confused about is the query plans on the slave server. Let's pretend we have a master server with: (2) E5-2660 CPU's (4) S3700 400GB SSD's 256GB of RAM And multiple slave servers that looks like: (1) E3-1290 CPU's (2) Intel 520 SSD's 32GB of RAM There's a significant difference between the master and slaves. Can you show us the explain analyze result on both master and slave? Does the ANALYZE command run on each system, and work differently on each system, or would the slave servers use the query plans from the master machine? ANALYZE can only be executed on master. Slave creates its own plan. However since statistics should be same as master, I guess if you get different plan, then postgresql.conf maybe different among master and slave. 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
