Re: [GENERAL] regclass and format('%I')
On Fri, Mar 13, 2015 at 12:18 PM, Jason Dusek wrote:
> Hi All,
>
> The difference in how format handles `regclass` and `name` seems like an
> inconsistency:
>
> WITH conversions(casts, format, result) AS (
> VALUES (ARRAY['name']::regtype[], '%I', format('%I',
> name('select'))),
>(ARRAY['name']::regtype[], '%L', format('%L',
> name('select'))),
>(ARRAY['name']::regtype[], '%s', format('%s',
> name('select'))),
>(ARRAY['regclass']::regtype[], '%I', format('%I',
> regclass('select'))),
>(ARRAY['regclass']::regtype[], '%L', format('%L',
> regclass('select'))),
>(ARRAY['regclass']::regtype[], '%s', format('%s',
> regclass('select'))),
>(ARRAY['regclass', 'name']::regtype[], '%I', format('%I',
> name(regclass('select',
>(ARRAY['regclass', 'name']::regtype[], '%L', format('%L',
> name(regclass('select',
>(ARRAY['regclass', 'name']::regtype[], '%s', format('%s',
> name(regclass('select'
> ) SELECT * FROM conversions;
> casts | format |result
> -++--
> {name} | %I | "select"
> {name} | %L | 'select'
> {name} | %s | select
> {regclass} | %I | """select"""
> {regclass} | %L | '"select"'
> {regclass} | %s | "select"
> {regclass,name} | %I | """select"""
> {regclass,name} | %L | '"select"'
> {regclass,name} | %s | "select"
>
> My assumption is that they both represent valid SQL identifiers, so it
> stands
> to reason that `%I` should result in a valid identifier for both of them
> (or
> neither one).
>
All three of the %I results are valid identifiers.
regclass performs the same conversion that %I performs. But since the
output of the regclass conversion is a valid identifier, with
double-quotes, the %I adds another pair of double-quotes and doubles-up the
existing pair thus leaving you with 6.
is a reserved word and thus can only be used as an identifier if
it is surrounded in double-quotes. name() doesn't care (not that it is
user-documented that I can find) about making its value usable as an
identifier so when its output goes through %I you get the expected value.
If you are going to use regclass you want to use %s to insert the result
into your string; not %I.
David J.
[GENERAL] bdr replication latency monitoring
I'm trying to follow the BDR monitoring docs: https://wiki.postgresql.org/wiki/BDR_Monitoring My postgres version string is (its from the 2nd Quadrant repo): PostgreSQL 9.4.0 on x86_64-unknown-linux-gnu, compiled by gcc (Ubuntu/Linaro 4.6.3-1ubuntu5) 4.6.3, 64-bit My BDR plugin is version 0.8.0beta1 From the docs, I've come up with this query: select slot_name, plugin, database, active, xmin, pg_get_transaction_committime(xmin) FROM pg_replication_slots ; BDR is working. When I run that query, the 'xmin' value is always null, even though there is activity on the database. I do/can get a catalog_xmin value. Should I expect the 'xmin' value to be null? Is there another way to monitor the replication latency when using BDR? Thanks, Steve Boyle -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] regclass and format('%I')
Hi All,
The difference in how format handles `regclass` and `name` seems like an
inconsistency:
WITH conversions(casts, format, result) AS (
VALUES (ARRAY['name']::regtype[], '%I', format('%I',
name('select'))),
(ARRAY['name']::regtype[], '%L', format('%L',
name('select'))),
(ARRAY['name']::regtype[], '%s', format('%s',
name('select'))),
(ARRAY['regclass']::regtype[], '%I', format('%I',
regclass('select'))),
(ARRAY['regclass']::regtype[], '%L', format('%L',
regclass('select'))),
(ARRAY['regclass']::regtype[], '%s', format('%s',
regclass('select'))),
(ARRAY['regclass', 'name']::regtype[], '%I', format('%I',
name(regclass('select',
(ARRAY['regclass', 'name']::regtype[], '%L', format('%L',
name(regclass('select',
(ARRAY['regclass', 'name']::regtype[], '%s', format('%s',
name(regclass('select'
) SELECT * FROM conversions;
casts | format |result
-++--
{name} | %I | "select"
{name} | %L | 'select'
{name} | %s | select
{regclass} | %I | """select"""
{regclass} | %L | '"select"'
{regclass} | %s | "select"
{regclass,name} | %I | """select"""
{regclass,name} | %L | '"select"'
{regclass,name} | %s | "select"
My assumption is that they both represent valid SQL identifiers, so it stands
to reason that `%I` should result in a valid identifier for both of them (or
neither one).
Kind Regards,
Jason Dusek
--
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] Rebuild streaming replication
Thank you - it worked beautifully. Dara On Thu, Mar 12, 2015 at 4:28 PM, Chander Ganesan wrote: > Hi Dara, > > A few things: > > 1. Replication is a binary process - that means that you can never set it > up with using any of the pg_dump type tools (i.e., pg_dumpall/pg_dump can > never be used.) > 2. PostgreSQL provides a special tool to make this process super-simple: > http://www.postgresql.org/docs/9.1/static/app-pgbasebackup.html > > So the steps should be as follows: > > 1. Don't touch the master (you really should never have to touch the > master's config/run state once you have replication setup, except for > performance tuning, etc.) > 2. Install PostgreSQL on the slave (if it already exists there, stop the > slave server.) > 3. On the slave server run the pg_basebackup tool (note: you may need to > delete the contents of the data directory on the slave first): > > pg_basebackup -D -c -P -h -U > > > 4. If your config files are in your data directory of the master, they'll > all be replicated over as well - if they are not then you'll need to copy > them over by hand. > > Hope that helps! > > Chander > > > > On 3/12/15 5:05 PM, Dara Unglaube wrote: > > We had two servers set up with streaming replication. I believe when we > did a full vacuum analyze to the database on the master, it caused the > slave to not be able to catch up (wal_keep_segments = 32, but still not > enough and had archive off). I am trying to re-set up the slave to > streaming replication. Below are the steps I took (attempted a couple times > now). Does anyone have any idea what I am doing wrong? The service is > unable to start after I copy and paste the files over. > >- Uninstalled and re-installed postgres on slave and master (v9.1.10) > > >- Stopped slave postgres service and added files and settings for >streaming replication to the slave (postgresql.cof, recovery.conf - which >all worked in the past set up) >- Loaded the master with a pg_dumpall backup file. >- Stopped the master postgres service. > - Copied the data directory from master to slave minus pg_xlog >folder, postgresql.conf and postgresql.pid files using rsync > - Attempted to start postgres on the slave and it is unable to start > - Postgres log: > - CDT LOG: entering standby mode > - CDT FATAL: the database is starting up (multiple times) > - CDT FATAL: could not connect to the primary server... (assume > this is ok because master has not been started yet) > - Event Viewer, Administrative Events > - Timed out waiting for server start up > > Any help, thoughts, comments, tips, etc would be greatly appreciated. > Thanks! > Dara > > > > -- > Chander Ganesan > Open Technology Group, Inc. > 11010 Lake Grove Blvd Ste. 100-307 > Morrisville, NC 27560 > 919-463-0999/877-258-8987http://www.otg-nc.com > >
Re: [GENERAL] DB Connections
On 3/13/2015 2:59 AM, Job wrote: The application i use need lots of static DB connections if lots is much over a 100 or so, I'd strongly advise using a connection pooler like pgbouncer or whatever is built into your language framework (for instance, Java frameworks like Tomcat have built in connection pools), and configuring your app to grab a connection from the pool, do a transaction and release the connection. -- john r pierce 37N 122W somewhere on the middle of the left coast -- 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] DB Connections
On 3/13/2015 6:51 AM, sameer malve ⎝⏠⏝⏠⎠ wrote: Just use pgtune utility it will give an o/p of u r postgres.conf depending on your machine hardware . I find on newer large hardware, pg_tune makes some over-the-top choices. -- john r pierce 37N 122W somewhere on the middle of the left coast -- 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] Basic Question on Point In Time Recovery
On 12 March 2015 at 12:31, Thomas Kellerer wrote: > 8.4 cannot run queries on the standby, you need to upgrade to a > supported/maintained version for this > (this feature was introduced in 9.0) > > In 9.x you can start the slave as a "hot standby" to allow read only queries > which is what pg_dump needs. > > You should really upgrade to a current version 9.4 or 9.3 Thought you were going to say that:-( Well, I guess we have to do it some time, and now there is a reason for it to happen sooner rather than later... But even if (OK, "though") I'm doing that, Steven's suggestion of making the dump to a ram file system, then filing it as a separate step, looks simple enough to be worth trying as a stop-gap... Robert. -- Robert Inder, Interactive Information Ltd, 3, Lauriston Gardens, Edinburgh EH3 9HH Registered in Scotland, Company no. SC 150689 Interactions speak louder than words -- 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] Fwd: Regarding pg_stat_statements
You can set log_min_duration_statement = 0 to log every statement. On Fri, Mar 13, 2015 at 3:38 AM, Sreerama Manoj wrote: > > Hi, > As we know that "pg_stat_statements" will monitor the queries after > normalizing the queries(Removes the values present in query). I want to > know is there a way to store those normalized values because I want to > check the type of data(values) ,range of data that is being hit to the > database. I am using Postgres 9.4 > > -- *Matt McGuire *| Manager, Data Management *mmcgu...@parchment.com * direct *916.367.6868* *ext. 1633 | mobile 916.549.9809* *3000 Lava Ridge Court, Suite 210 Roseville, CA 95661* *Parchment *| Turn Credentials into Opportunities www.parchment.com
Re: [GENERAL] Basic Question on Point In Time Recovery
> The thing is you can use desktop class machines for the slave. If you do > not have spare machines I would suggest a desktop class machine with big > RAM and whatever disks you need for the DB plus an extra disk to pg_dump to > ( so pg_dump does not compete with DB for the db disks, this really kills > performance ). Replication slaves do not need that much RAM ( as the only > query it is going to run is the pg_dump ones, but desktop ram is cheap ). > We did this with a not so powerful desktop with an extra sata disk to store > the pg_dumps and it worked really well, and we are presently using two > servers, using one of the extra gigabit interfaces with a crossover cable > for the replication connection plus an extra sata disk to make hourly > pg_dumps and it works quite well. If load on the backup server becomes an issue you might be able to make incremental pg_dump's onto tmpfs. Advantage there is that the dump iteslf has effectively no write I/O overhead: you can dump to tmpfs and then [bg]zip to stable storage w/o beating up the disks, which becomes a real problem with comodity-grade hardware. -- Steven Lembark 3646 Flora Pl Workhorse Computing St Louis, MO 63110 lemb...@wrkhors.com +1 888 359 3508 -- 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] DB Connections
On 03/13/2015 02:59 AM, Job wrote: Hello, i have just installed (on a Centos 6.5 x64 machine) Postgresql 9 latest stable version. The application i use need lots of static DB connections: in the 9 version, how many connections can be declared in postgresql.conf? I searched for a max number but i noticed it is not specified. Without some concrete information this will difficult to answer. 1) How many is lots? 2) Why do you need static connections? 3) What are the hardware specifications for your machine? Thank you, Francesco -- 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] DB Connections
Hello , Just use pgtune utility it will give an o/p of u r postgres.conf depending on your machine hardware . Regards, Sameer On Mar 13, 2015 5:45 PM, "Vick Khera" wrote: > > On Fri, Mar 13, 2015 at 5:59 AM, Job wrote: > >> The application i use need lots of static DB connections: in the 9 >> version, how many connections can be declared in postgresql.conf? >> I searched for a max number but i noticed it is not specified. >> > > It depends on how much shared memory you give to the postgres process. How > many are you looking to have? >
Re: [GENERAL] DB Connections
On Fri, Mar 13, 2015 at 5:59 AM, Job wrote: > The application i use need lots of static DB connections: in the 9 > version, how many connections can be declared in postgresql.conf? > I searched for a max number but i noticed it is not specified. > It depends on how much shared memory you give to the postgres process. How many are you looking to have?
[GENERAL] DB Connections
Hello, i have just installed (on a Centos 6.5 x64 machine) Postgresql 9 latest stable version. The application i use need lots of static DB connections: in the 9 version, how many connections can be declared in postgresql.conf? I searched for a max number but i noticed it is not specified. Thank you, Francesco -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] DB Connections
Hello, i have just installed (on a Centos 6.5 x64 machine) Postgresql 9 latest stable version. The application i use need lots of static DB connections: in the 9 version, how many connections can be declared in postgresql.conf? I searched for a max number but i noticed it is not specified. Thank you, Francesco -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Fwd: Regarding pg_stat_statements
Hi, As we know that "pg_stat_statements" will monitor the queries after normalizing the queries(Removes the values present in query). I want to know is there a way to store those normalized values because I want to check the type of data(values) ,range of data that is being hit to the database. I am using Postgres 9.4
