Re: [GENERAL] Question about copy from with timestamp format

[Jim Nasby]

On 7/30/15 3:09 PM, Alban Hertroys wrote:

COPY is a bit special, I'm afraid. For starters, although it works_like_  doing 
a bunch of INSERTs, it doesn't perform actual INSERTs. Apparently, that also 
means it won't fire an INSERT rule and thus can't be used with an updatable 
view. There are no rules on such a view (rules rewrite the query) that would 
work for COPY.

Now perhaps that sounds like a COPY rule is warranted for cases like these, but 
that doesn't help, exactly because the COPY command has no place in its syntax 
for expressions (such as this type conversion). INSERT does, hence we can write 
a rule for it…

In hindsight it all makes sense. That doesn't bring you any closer to a 
solution, unfortunately.


By the way, if you're desperate enough to make this work during copy, 
you could create a new type that understands that time format. It'd 
involve some C coding though.


It would be nice if there was a way to do transforms during COPY. I 
vaguely remember some discussion of that on hackers some time ago.

--
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Data in Trouble? Get it in Treble! http://BlueTreble.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] Transaction ID Wraparound Monitoring

[Jim Nasby]

On 8/4/15 2:47 AM, Jan Keirse wrote:

CAST (age(relfrozenxid) AS real) / CAST(trunc(((2^32)/2)-1-100) AS real)
>AS perc_until_wraparound_server_freeze
>
>
>(Note that we do this at the table level rather than the database level like
>you did, though, so that we have the information we need to tune the
>settings for individual tables.)

Thanks for the correction regarding the 1.000.000 safety margin! I
chose to monitor only the total value value to limit the amount of
extra metrics in the monitoring database. In case the value increased
we'll execute the queries to find out what table(s) is/are causing the
problem interactively.


Something to be aware of; with certain workloads you can actually run 
out of MXIDs faster than XIDs, and I don't think there's any easy way to 
monitor MXID consumption.


BTW, you might find my talk from pgCon or the related video useful:

http://www.pgcon.org/2015/schedule/events/829.en.html
https://www.youtube.com/watch?v=b1fcvkl0ffQ
--
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Data in Trouble? Get it in Treble! http://BlueTreble.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] Fwd: postgresql databases disappearing and reappearing and disappearing on localhost - Postgresql x649.3 on Windows 8.1 (64)

[Adrian Klaver]

On 08/04/2015 08:12 AM, Killian Driscoll wrote:


On 4 August 2015 at 17:02, Adrian Klaver mailto:adrian.kla...@aklaver.com>> wrote:

On 08/04/2015 07:48 AM, Killian Driscoll wrote:

CCing list

FYI, I will be away from my computer. Someone else will have to
follow up to my questions below.



 So that is not the server you set up, correct?


I am using the postgresql on localhost 5432. The OpenGeo\pgsql is
something I set up to test, but ended up not using it. It runs on a
localhost 8000, but could uninstall it as I'm not going to use that
program.


 Can you access it and see what is in it?


When I access it on localhost 8000 it shows the Geoserver with
the test
tables I included from my main db


So in pgAdmin do you see both servers?


In pgadmin only one server - 5432 - is listed. (I don't remember how I
imported the table to the 8000 Geoserver: I think it was via QGIS)



So what happens if you use the command line program psql and connect to 
the server on port 5432?


Assuming you can connect what does \l from the psql command line show?



--
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] Bi-Directional replication(BDR)

[Joshua D. Drake]

On 08/04/2015 12:47 PM, clingare...@vsoftcorp.com wrote:

Hi,

Please help me on:

what is the use of bidirectional replication in PostgreSQL?

How BDR works?

how to setup BDR?

on which versions BDR works?



Please do not cross post. What you are looking for is here:

http://bdr-project.org/docs/stable/index.html

JD


--
Command Prompt, Inc. - http://www.commandprompt.com/  503-667-4564
PostgreSQL Centered full stack support, consulting and development.
Announcing "I'm offended" is basically telling the world you can't
control your own emotions, so everyone else should do it for you.


--
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] Get additional constraint information

[Igor Neyman]

From: pgsql-general-ow...@postgresql.org 
[mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Tobias Fielitz
Sent: Monday, August 03, 2015 9:56 PM
To: pgsql-general@postgresql.org
Subject: [GENERAL] Get additional constraint information

select * from information_schema.table_constraints where
table_name=‘install_crash’;

Results here: http://imgur.com/YymSvBS

How can I get more information about these constraints, especially on which 
columns the not null constraints are?

Thanks

Tobias Fielitz
CTO StreetHawk
+61404267511
tobias.fielitz (skype)

--

select column_name, is_nullable from information_schema.columns where 
table_name = 'install_crash';

Regards,
Igor Neyman

Re: [GENERAL] Bi-Directional replication(BDR)

[Brian Dunavant]

I would suggest going to  http://bdr-project.org/docs/stable/index.html

On Tue, Aug 4, 2015 at 3:47 PM,   wrote:
> Hi,
>
>
>
> Please help me on:
>
>
>
> what is the use of bidirectional replication in PostgreSQL?
>
> How BDR works?
>
> how to setup BDR?
>
> on which versions BDR works?
>
>
>
> Thanks & regards,
>
> Chandra kiran
>
>
>
> Please do not print this email unless it is absolutely necessary. This email
> and any files transmitted with it are confidential and intended solely for
> the use of the individual or entity to which they are addressed. If you are
> not the intended recipient, you should not disseminate, distribute or copy
> this e-mail. Please notify the sender immediately by e-mail and destroy all
> copies of this message and any attachments. Any views or opinions presented
> in this email are solely those of the author and do not necessarily
> represent those of the company. Warning: Although the company has taken
> reasonable precautions to ensure no viruses are present in this email, the
> company cannot accept responsibility for any loss or damage arising from the
> use of this email or attachments.


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Bi-Directional replication(BDR)

[clingareddy]

Hi,

Please help me on:

what is the use of bidirectional replication in PostgreSQL?
How BDR works?
how to setup BDR?
on which versions BDR works?

Thanks & regards,
Chandra kiran









Please do not print this email unless it is absolutely necessary.

This email and any files transmitted with it are confidential and intended 
solely for the use of the individual or entity to which they are addressed. If 
you are not the intended recipient, you should not disseminate, distribute or 
copy this e-mail. Please notify the sender immediately by e-mail and destroy 
all copies of this message and any attachments. Any views or opinions presented 
in this email are solely those of the author and do not necessarily represent 
those of the company.

Warning: Although the company has taken reasonable precautions to ensure no 
viruses are present in this email, the company cannot accept responsibility for 
any loss or damage arising from the use of this email or attachments.

[GENERAL] Get additional constraint information

[Tobias Fielitz]

select * from information_schema.table_constraints where
table_name=‘install_crash’;



Results here: http://imgur.com/YymSvBS


How can I get more information about these constraints, especially on which 
columns the not null constraints are?


Thanks

Tobias FielitzCTO StreetHawk
+61404267511
tobias.fielitz (skype)

Re: [GENERAL] Fwd: postgresql databases disappearing and reappearing and disappearing on localhost - Postgresql x649.3 on Windows 8.1 (64)

[Killian Driscoll]

On 4 August 2015 at 17:02, Adrian Klaver  wrote:

> On 08/04/2015 07:48 AM, Killian Driscoll wrote:
>
> CCing list
>
> FYI, I will be away from my computer. Someone else will have to follow up
> to my questions below.
>
>>
>>
> So that is not the server you set up, correct?
>>
>>
>> I am using the postgresql on localhost 5432. The OpenGeo\pgsql is
>> something I set up to test, but ended up not using it. It runs on a
>> localhost 8000, but could uninstall it as I'm not going to use that
>> program.
>>
>>
>> Can you access it and see what is in it?
>>
>>
>> When I access it on localhost 8000 it shows the Geoserver with the test
>> tables I included from my main db
>>
>
> So in pgAdmin do you see both servers?


In pgadmin only one server - 5432 - is listed. (I don't remember how I
imported the table to the 8000 Geoserver: I think it was via QGIS)

>
>
>
>>
>>
>> What does the Postgres log show when you connect? - where can I
>> find the
>> postgres log?
>>
>>
>> http://www.pgadmin.org/docs/1.20/status.html
>>
>>
>> The log shows the main db only:
>>
>> FATALthe database system is starting up
>> LOGdatabase system was interrupted; last known up at 2015-07-31
>> 03:37:41 EDT
>> LOGdatabase system was not properly shut down; automatic recovery in
>> progress
>> LOGrecord with zero length at 0/5B392220
>> LOGredo is not required
>> LOGdatabase system is ready to accept connections
>> LOGautovacuum launcher started
>> ERRORschema "pgagent" does not exist
>> STATEMENTSELECT pgagent.pgagent_schema_version()
>> ERRORrelation "pgagent.pga_jobagent" does not exist at character 59
>> STATEMENTINSERT INTO pga_tmp_zombies (jagpid) SELECT jagpid   FROM
>> pgagent.pga_jobagent AG   LEFT JOIN pg_stat_activity PA ON jagpid=pid
>> WHERE pid IS NULL
>> ERRORrelation "pgagent.pga_jobagent" does not exist at character 13
>> STATEMENTINSERT INTO pgagent.pga_jobagent (jagpid, jagstation)
>> SELECT pg_backend_pid(), 'KillianAcer.homestation'
>> ERRORschema "pgagent" does not exist
>> STATEMENTSELECT pgagent.pgagent_schema_version()
>> LOGcould not receive data from client: No connection could be made
>> because the target machine actively refused it.
>>
>> ERRORsyntax error at or near " " at character 1
>> STATEMENT
>>  l
>>  "\l" meta command"\l" meta command"\l" meta command
>>  select osm from pg_database
>>  select osm from pg_database;
>> LOGcould not receive data from client: No connection could be made
>> because the target machine actively refused it.
>>
>
> Well this seems to be your problem.
>
> Have you changed your pg_hba.conf recently?
>

I don't think so: if that file is here C:\Program Files\PostgreSQL\9.3\data
I don't think I made any changes

>
>
>> LOGinvalid length of startup packet
>> ERRORrelation "public.gt_pk_metadata" does not exist at character 15
>> STATEMENTSELECT * FROM public.GT_PK_METADATA WHERE 1 = 0
>>
>>
>>
>> What user are you connecting as, and what privileges does that
>> user
>> have? - I had set it up as one user/owner.
>>
>>
>> So from here:
>>
>> http://www.pgadmin.org/docs/1.20/main.html
>>
>> You can find the users(roles) and their privileges.
>>
>>
>> The is one user - me as postgres, a superuser
>>
>>
>>
>>
>> Killian Driscoll
>> Banting Postdoctoral Fellow
>> Département d'anthropologie
>> Université de Montréal
>>
>>
>>
>>
>> --
>> Adrian Klaver
>> adrian.kla...@aklaver.com 
>>
>>
>>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>

Re: [GENERAL] Fwd: postgresql databases disappearing and reappearing and disappearing on localhost - Postgresql x649.3 on Windows 8.1 (64)

[Adrian Klaver]

On 08/04/2015 07:48 AM, Killian Driscoll wrote:

CCing list

FYI, I will be away from my computer. Someone else will have to follow 
up to my questions below.





So that is not the server you set up, correct?


I am using the postgresql on localhost 5432. The OpenGeo\pgsql is
something I set up to test, but ended up not using it. It runs on a
localhost 8000, but could uninstall it as I'm not going to use that
program.


Can you access it and see what is in it?


When I access it on localhost 8000 it shows the Geoserver with the test
tables I included from my main db


So in pgAdmin do you see both servers?





What does the Postgres log show when you connect? - where can I
find the
postgres log?


http://www.pgadmin.org/docs/1.20/status.html


The log shows the main db only:

FATALthe database system is starting up
LOGdatabase system was interrupted; last known up at 2015-07-31
03:37:41 EDT
LOGdatabase system was not properly shut down; automatic recovery in
progress
LOGrecord with zero length at 0/5B392220
LOGredo is not required
LOGdatabase system is ready to accept connections
LOGautovacuum launcher started
ERRORschema "pgagent" does not exist
STATEMENTSELECT pgagent.pgagent_schema_version()
ERRORrelation "pgagent.pga_jobagent" does not exist at character 59
STATEMENTINSERT INTO pga_tmp_zombies (jagpid) SELECT jagpid   FROM
pgagent.pga_jobagent AG   LEFT JOIN pg_stat_activity PA ON jagpid=pid
WHERE pid IS NULL
ERRORrelation "pgagent.pga_jobagent" does not exist at character 13
STATEMENTINSERT INTO pgagent.pga_jobagent (jagpid, jagstation)
SELECT pg_backend_pid(), 'KillianAcer.homestation'
ERRORschema "pgagent" does not exist
STATEMENTSELECT pgagent.pgagent_schema_version()
LOGcould not receive data from client: No connection could be made
because the target machine actively refused it.

ERRORsyntax error at or near " " at character 1
STATEMENT
 l
 "\l" meta command"\l" meta command"\l" meta command
 select osm from pg_database
 select osm from pg_database;
LOGcould not receive data from client: No connection could be made
because the target machine actively refused it.


Well this seems to be your problem.

Have you changed your pg_hba.conf recently?



LOGinvalid length of startup packet
ERRORrelation "public.gt_pk_metadata" does not exist at character 15
STATEMENTSELECT * FROM public.GT_PK_METADATA WHERE 1 = 0



What user are you connecting as, and what privileges does that user
have? - I had set it up as one user/owner.


So from here:

http://www.pgadmin.org/docs/1.20/main.html

You can find the users(roles) and their privileges.


The is one user - me as postgres, a superuser




Killian Driscoll
Banting Postdoctoral Fellow
Département d'anthropologie
Université de Montréal




--
Adrian Klaver
adrian.kla...@aklaver.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] Fwd: postgresql databases disappearing and reappearing and disappearing on localhost - Postgresql x649.3 on Windows 8.1 (64)

[Adrian Klaver]

On 08/04/2015 06:48 AM, Killian Driscoll wrote:

Please do not top post, thanks. - no idea what that means.


See here:

https://en.wikipedia.org/wiki/Posting_style#Top-posting

vs

https://en.wikipedia.org/wiki/Posting_style#Interleaved_style

or

https://en.wikipedia.org/wiki/Posting_style#Bottom-posting

which are the preferred styles for this list.



And you have verified this my looking at the Windows process monitor? -
you mean task manager: if so, I also see Boundless\OpenGeo\pgsql\bin
running a postgresql server


So that is not the server you set up, correct?

Can you access it and see what is in it?



What does the Postgres log show when you connect? - where can I find the
postgres log?


http://www.pgadmin.org/docs/1.20/status.html



What user are you connecting as, and what privileges does that user
have? - I had set it up as one user/owner.


So from here:

http://www.pgadmin.org/docs/1.20/main.html

You can find the users(roles) and their privileges.



Killian Driscoll
Banting Postdoctoral Fellow
Département d'anthropologie
Université de Montréal





--
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] scaling postgres - can child tables be in a different tablespace?

[John R Pierce]

On 8/4/2015 6:14 AM, Melvin Davidson wrote:
As additional advice, to get the best performance, you will want all 
your tablespaces to be on separate spindles/disks.

EG: disk1/tblspc1
disk2/tblspc2
disk3/tblspc3
...
disk99/tblspc99



actually, I find to get best performance most often, stripe all the 
disks together and put everything on the same big volume, that way all 
IO is evenly distributed.   otherwise you'll find too much IO on some 
tables/partitions, and not enoguh on others, so most of the disks are 
idle most of the time.



--
john r pierce, recycling bits in santa cruz



--
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: postgresql databases disappearing and reappearing and disappearing on localhost - Postgresql x649.3 on Windows 8.1 (64)

[Adrian Klaver]

On 08/04/2015 06:25 AM, Killian Driscoll wrote:

CCing list

Please do not top post, thanks.


All the databases are one one server on localhost 5432


And you have verified this my looking at the Windows process monitor?

The reason I keep asking is that is looks very much like you are 
connecting to two different clusters. One that has only your main 
database and one with it and the four test databases.


Does the data in the main database look the same in both cases?

What does the Postgres log show when you connect?

Another theory is that this is a user privileges thing.

What user are you connecting as, and what privileges does that user have?





Killian Driscoll
Banting Postdoctoral Fellow
Département d'anthropologie
Université de Montréal

umontreal.academia.edu/KillianDriscoll

www.lithicsireland.ie 
ca.linkedin.com/in/killiandriscoll


On 4 August 2015 at 15:23, Adrian Klaver mailto:adrian.kla...@aklaver.com>> wrote:

On 08/04/2015 01:58 AM, Killian Driscoll wrote:

I am using postgresql 9.3 with pgadmin III, and Access 2013 as a
front
end for data entry using (ODBC connection) linked tables, on a
Windows
8.1 (64).

I have one main database I am developing on a localhost:5432,
with four
other test databases I had on the same localhost. A few weeks
ago the
four test databases disappeared from the list in pgadmin III and
using
the shell command to list the databases, only the main database
showed up.


So do you have more then one Postgres cluster/instance on the machine?




A week ago, the four test databases reappeared and were apparently
functional (in pgadmin I viewed the tables and they appeared
fine). I
turned off pgadmin and restarted, and the four test dataabases
disappeared again. >
Today, I turned on pgadmin and the four appeared again - I took a
screenshot to confirm I am not hallucinating (!). Again, I
turned off
pgadmin, and restarted and they disappeared again.



--
Adrian Klaver
adrian.kla...@aklaver.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] Fwd: postgresql databases disappearing and reappearing and disappearing on localhost - Postgresql x649.3 on Windows 8.1 (64)

[Adrian Klaver]

On 08/04/2015 01:58 AM, Killian Driscoll wrote:

I am using postgresql 9.3 with pgadmin III, and Access 2013 as a front
end for data entry using (ODBC connection) linked tables, on a Windows
8.1 (64).

I have one main database I am developing on a localhost:5432, with four
other test databases I had on the same localhost. A few weeks ago the
four test databases disappeared from the list in pgadmin III and using
the shell command to list the databases, only the main database showed up.


So do you have more then one Postgres cluster/instance on the machine?




A week ago, the four test databases reappeared and were apparently
functional (in pgadmin I viewed the tables and they appeared fine). I
turned off pgadmin and restarted, and the four test dataabases
disappeared again. >
Today, I turned on pgadmin and the four appeared again - I took a
screenshot to confirm I am not hallucinating (!). Again, I turned off
pgadmin, and restarted and they disappeared again.




--
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] scaling postgres - can child tables be in a different tablespace?

[Melvin Davidson]

As additional advice, to get the best performance, you will want all your
tablespaces to be on separate spindles/disks.
EG: disk1/tblspc1
disk2/tblspc2
disk3/tblspc3
...
disk99/tblspc99

On Tue, Aug 4, 2015 at 5:31 AM, Albe Laurenz 
wrote:

> Chris Withers wrote:
> > This raises an interesting question: can a child table be in a different
> > tablespace to its parent and other children of that parent?
>
> Yes.
>
> Inheritance is a logical concept and is independent of physical placement.
>
> Yours,
> Laurenz Albe
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>



-- 
*Melvin Davidson*
I reserve the right to fantasize.  Whether or not you
wish to share my fantasy is entirely up to you.

Re: [GENERAL] scaling postgres - can child tables be in a different tablespace?

[Albe Laurenz]

Chris Withers wrote:
> This raises an interesting question: can a child table be in a different
> tablespace to its parent and other children of that parent?

Yes.

Inheritance is a logical concept and is independent of physical placement.

Yours,
Laurenz Albe

-- 
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: postgresql databases disappearing and reappearing and disappearing on localhost - Postgresql x649.3 on Windows 8.1 (64)

[dinesh kumar]

On Tue, Aug 4, 2015 at 2:28 PM, Killian Driscoll 
wrote:

> I am using postgresql 9.3 with pgadmin III, and Access 2013 as a front end
> for data entry using (ODBC connection) linked tables, on a Windows 8.1 (64).
>
> OK.

> I have one main database I am developing on a localhost:5432, with four
> other test databases I had on the same localhost. A few weeks ago the four
> test databases disappeared from the list in pgadmin III and using the shell
> command to list the databases, only the main database showed up.
>
> Could you be more specific about this. Using shell command to list the
database, you got one database OR all databases (?)

> A week ago, the four test databases reappeared and were apparently
> functional (in pgadmin I viewed the tables and they appeared fine). I
> turned off pgadmin and restarted, and the four test dataabases disappeared
> again.
>
Did you restarted the DB after pgAdmin turnoff.

> Today, I turned on pgadmin and the four appeared again - I took a
> screenshot to confirm I am not hallucinating (!). Again, I turned off
> pgadmin, and restarted and they disappeared again.
>
Regards,
Dinesh

[GENERAL] Fwd: postgresql databases disappearing and reappearing and disappearing on localhost - Postgresql x649.3 on Windows 8.1 (64)

[Killian Driscoll]

I am using postgresql 9.3 with pgadmin III, and Access 2013 as a front end
for data entry using (ODBC connection) linked tables, on a Windows 8.1 (64).

I have one main database I am developing on a localhost:5432, with four
other test databases I had on the same localhost. A few weeks ago the four
test databases disappeared from the list in pgadmin III and using the shell
command to list the databases, only the main database showed up.

A week ago, the four test databases reappeared and were apparently
functional (in pgadmin I viewed the tables and they appeared fine). I
turned off pgadmin and restarted, and the four test dataabases disappeared
again.

Today, I turned on pgadmin and the four appeared again - I took a
screenshot to confirm I am not hallucinating (!). Again, I turned off
pgadmin, and restarted and they disappeared again.

Re: [GENERAL] scaling postgres

[Chris Withers]

On 04/08/2015 09:11, Seref Arikan wrote:



I work in healthcare and patient centric records let me consider

multiple servers for lots and lots of patients. The
engineering team
from instagram has been sharing their experience with
postgres, which is
possible due to their domain model.


I'll have a google, but do you have the Instagram links you're
thinking of?


Nope, sorry, Google is your friend :)


For the benefit of the archives:

http://instagram-engineering.tumblr.com/post/40781627982/handling-growth-with-postgres-5-tips-from

http://instagram-engineering.tumblr.com/post/10853187575/sharding-ids-at-instagram

cheers,

Chris

Re: [GENERAL] scaling postgres

[Seref Arikan]

On Tue, Aug 4, 2015 at 9:06 AM, Chris Withers 
wrote:

> On 03/08/2015 08:34, Seref Arikan wrote:
>
>>
>>
>> At what point does postgres stop scaling?
>> What happens when the computational load no longer fits on one
>> machine? What are the options then?
>>
>>
>> I think it is hard to come up with blanket responses to generic
>> questions such as  "What happens when the computational load no longer
>> fits on one machine?"
>>
>
> Of course, I guess I'm looking out for the pain points that people
> commonly hit with chunky postgres installs...
>
> I work in healthcare and patient centric records let me consider
>> multiple servers for lots and lots of patients. The engineering team
>> from instagram has been sharing their experience with postgres, which is
>> possible due to their domain model.
>>
>
> I'll have a google, but do you have the Instagram links you're thinking of?
>
>
> Nope, sorry, Google is your friend :)


> cheers,
>
> Chris
>
>
> --
> 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] scaling postgres

[Chris Withers]

On 03/08/2015 08:34, Seref Arikan wrote:



At what point does postgres stop scaling?
What happens when the computational load no longer fits on one
machine? What are the options then?


I think it is hard to come up with blanket responses to generic
questions such as  "What happens when the computational load no longer
fits on one machine?"


Of course, I guess I'm looking out for the pain points that people 
commonly hit with chunky postgres installs...



I work in healthcare and patient centric records let me consider
multiple servers for lots and lots of patients. The engineering team
from instagram has been sharing their experience with postgres, which is
possible due to their domain model.


I'll have a google, but do you have the Instagram links you're thinking of?

cheers,

Chris


--
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] scaling postgres - can child tables be in a different tablespace?

[Chris Withers]

On 03/08/2015 08:40, Jony Cohen wrote:

Servers now days reach very impressive write speeds and at rather low
prices - it's simpler to split the write to 2 tables on different
tablespaces/devices than 2 servers.
This raises an interesting question: can a child table be in a different 
tablespace to its parent and other children of that parent?


cheers,

Chris



--
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] Transaction ID Wraparound Monitoring

[Jan Keirse]

On Tue, Aug 4, 2015 at 6:24 AM, William Dunn  wrote:
> Hello Jan,
>
> I think your calculation is slightly off because per the docs when
> PostgreSQL comes within 1 million of the age at which an actual wraparound
> occurs it will go into the safety shutdown mode. Thus the calculation should
> be ((2^32)-1)/2-100 rather than just ((2^32)-1)/2 as I think you are
> using.
>
> When I first started building out my group's PostgreSQL monitoring solution
> I too found the wording of transaction freeze to be a bit difficult to
> understand. For my team's internal documentation I have summarized it as
> follows, I hope it might be more clear:
>
> ...normal XIDs are compared using modulo-2^32 arithmetic, which means that
> ~(2^32-1)/2- transactions appear in the future and ~(2^32-1)/2 transactions
> appear in the past.
>
> This [Transaction ID freeze] behavior of autovacuum is primarily dependent
> on the settings autovacuum_freeze_table_age and autovacuum_freeze_max_age,
> which are set as database defaults but can also be specified on a per table
> basis (as storage parameters in CREATE TABLE or ALTER TABLE)
>
> When a table's oldest transaction reaches autovacuum_freeze_table_age, the
> next autovacuum that is performed on that table will be a vacuum freeze
>
> PostgreSQL implicitly caps autovacuum_freeze_table_age at
> 0.95*autovacuum_freeze_max_age.
>
> When a table reaches autovacuum_freeze_max_age PostgreSQL will force an
> autovacuum freeze on that table, even if the table would not otherwise be
> autovacuumed or autovacuum is disabled.
>
> PostgreSQL implicitly caps autovacuum_freeze_max_age at 2 billion
> (20)
>
> The actual age that a wraparound occurs is ((2^32)/2)-1. When a PostgreSQL
> database comes within 1 million of this age (2^32/2-1-100) the database
> will go into the safety shutdown mode" and no longer accept commands,
> including the vacuum commands, and your only recovery option is to stop the
> server and use a single-user backend (where shutdown mode is not enforced)
> to execute VACUUM. This should, obviously, be avoided at all costs.
>
> References:
>
> http://www.PostgreSQL.org/docs/current/static/routine-vacuuming.html#VACUUM-FOR-WRAPAROUND
> http://www.PostgreSQL.org/docs/current/static/runtime-config-client.html#GUC-VACUUM-FREEZE-TABLE-AGE
> http://www.PostgreSQL.org/docs/current/static/runtime-config-autovacuum.html#GUC-AUTOVACUUM-FREEZE-MAX-AGE
>
>
> Based on the above explanation we consider the following to be the most
> correct check for how close you are to an actual wraparound freeze:
>
> CAST (age(relfrozenxid) AS real) / CAST(trunc(((2^32)/2)-1-100) AS real)
> AS perc_until_wraparound_server_freeze
>
>
> (Note that we do this at the table level rather than the database level like
> you did, though, so that we have the information we need to tune the
> settings for individual tables.)

Thanks for the correction regarding the 1.000.000 safety margin! I
chose to monitor only the total value value to limit the amount of
extra metrics in the monitoring database. In case the value increased
we'll execute the queries to find out what table(s) is/are causing the
problem interactively.



> However it is better to set autovacuum max freeze age well below that value
> and monitor that instead. Autovacuum should always do a vacuum freeze for a
> table that has exceeded max freeze age, and if you are monitoring for that
> you should avoid a wrap around freeze:
>
> CAST (age(relfrozenxid) AS real) / CAST ((least(autovacuum_freeze_max_age,
> 20)) AS real) AS perc_until_freeze_max_age
>
>
> And ensure that value does not exceed 100%. Though it is important to note
> that max freeze age can be set on a per table basis, so to get the true
> autovacuum_freeze_max_age of a table (or the real max of the database) you
> would need to check the reloptions field of pg_class for that table and only
> if there is no value specified for '%autovacuum_freeze_table_age%' use
> current_setting('autovacuum_freeze_max_age')

I'll see to add this one to the monitoring too. Thanks for your clarifications!

-- 


 DISCLAIMER 

http://www.tvh.com/glob/en/email-disclaimer

"This message is delivered to all addressees subject to the conditions
set forth in the attached disclaimer, which is an integral part of this
message."


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general