Re: [GENERAL] Schema Information .

[Brahmam Eswar]

Thanks David for your quick response.

I'm using below query to pull the schema information ,but the count of rows
in table is giving wrong ,i can see the count of records using  select
count(*) from  . How do we get an exact number of rows in table.


SELECT  C.relname AS Table_Name,
C.relnatts AS NUM_COLS,
C.reltuples::bigint AS NUM_ROWS,
C.relhastriggers AS Has_Triggers,
C.relhasindex AS HAS_INDEX
FROM pg_class C
JOIN pg_namespace N ON (N.oid = C.relnamespace)
WHERE   n.nspname='ap' and   C.relkind='r' ORDER BY C.relname ;

On Mon, Dec 4, 2017 at 9:20 PM, Melvin Davidson 
wrote:

>
>
> On Mon, Dec 4, 2017 at 9:17 AM, Brahmam Eswar 
> wrote:
>
>> Hi ,
>>
>> Is there anyway to pull the complete information of tables in a
>> particular schema .
>>
>> Table_Name ,Num_Rows,Columns,Indexed_Columns,Triggers.
>>
>>
>>
>> The context of this request is ,we are migrating the database from Oracle
>> to PostgreSQl,,so we need to verify the data after perform data migration
>> from oracle.
>>
>>
>> --
>> Thanks & Regards,
>> Brahmeswara Rao J.
>>
>
> >Is there anyway to pull the complete information of tables in a
> particular schema .
>
> The following query will give you all the tables and columns in a schema:
>
> SELECT n.nspname AS schema,
>  c.relname AS table,
>  a.attname AS column,
>  a.attnum   AS col_pos
>   FROM pg_namespace n
> JOIN  pg_class c ON c.relnamespace = n.oid
> JOIN  pg_attribute a ON a.attrelid = c.oid
>  WHERE n.nspname = 'your_schema'
>AND relkind = 'r'
>AND a.attnum > 0
> ORDER BY 1, 2, 4;
>
> To obtain information about indexed columns and triggers, you will also
> need to query
> pg_index and pg_trigger
>
> https://www.postgresql.org/docs/9.6/static/catalogs.html
>
> You might also find it easier to look at the information_schema
>
> 
> https://www.postgresql.org/docs/9.6/static/information-schema.html
>
>
>
> --
> *Melvin Davidson*
> I reserve the right to fantasize.  Whether or not you
> wish to share my fantasy is entirely up to you.
>



-- 
Thanks & Regards,
Brahmeswara Rao J.

Replica on low-bandwitdh network

[Job]

Hi guys,

we have some postgreswl 9.6.1 servers in two different continent.
Latency is from 180 to 250 ms between the two sites.

Actually we use Rubyrep for off-site replication, since servers are completely 
independent in case of connectivity loss.

We are experiencing some problems with lock tables (rubyrep works with trigger 
to commit changes to the other server),
we think for the uncommon latency.

Which is, for you, the best replication module for Postgresql?
Bucardo? Something else?

Thank you, very best!
F

Re: [GENERAL] Schema Information .

[Melvin Davidson]

On Tue, Dec 5, 2017 at 4:09 AM, Brahmam Eswar  wrote:

> Thanks David for your quick response.
>
> I'm using below query to pull the schema information ,but the count of
> rows in table is giving wrong ,i can see the count of records using  select
> count(*) from  . How do we get an exact number of rows in table.
>
>
> SELECT  C.relname AS Table_Name,
> C.relnatts AS NUM_COLS,
> C.reltuples::bigint AS NUM_ROWS,
> C.relhastriggers AS Has_Triggers,
> C.relhasindex AS HAS_INDEX
> FROM pg_class C
> JOIN pg_namespace N ON (N.oid = C.relnamespace)
> WHERE   n.nspname='ap' and   C.relkind='r' ORDER BY C.relname ;
>
> On Mon, Dec 4, 2017 at 9:20 PM, Melvin Davidson 
> wrote:
>
>>
>>
>> On Mon, Dec 4, 2017 at 9:17 AM, Brahmam Eswar 
>> wrote:
>>
>>> Hi ,
>>>
>>> Is there anyway to pull the complete information of tables in a
>>> particular schema .
>>>
>>> Table_Name ,Num_Rows,Columns,Indexed_Columns,Triggers.
>>>
>>>
>>>
>>> The context of this request is ,we are migrating the database from
>>> Oracle to PostgreSQl,,so we need to verify the data after perform data
>>> migration from oracle.
>>>
>>>
>>> --
>>> Thanks & Regards,
>>> Brahmeswara Rao J.
>>>
>>
>> >Is there anyway to pull the complete information of tables in a
>> particular schema .
>>
>> The following query will give you all the tables and columns in a schema:
>>
>> SELECT n.nspname AS schema,
>>  c.relname AS table,
>>  a.attname AS column,
>>  a.attnum   AS col_pos
>>   FROM pg_namespace n
>> JOIN  pg_class c ON c.relnamespace = n.oid
>> JOIN  pg_attribute a ON a.attrelid = c.oid
>>  WHERE n.nspname = 'your_schema'
>>AND relkind = 'r'
>>AND a.attnum > 0
>> ORDER BY 1, 2, 4;
>>
>> To obtain information about indexed columns and triggers, you will also
>> need to query
>> pg_index and pg_trigger
>>
>> https://www.postgresql.org/docs/9.6/static/catalogs.html
>>
>> You might also find it easier to look at the information_schema
>>
>> 
>> https://www.postgresql.org/docs/9.6/static/information-schema.html
>>
>>
>>
>> --
>> *Melvin Davidson*
>> I reserve the right to fantasize.  Whether or not you
>> wish to share my fantasy is entirely up to you.
>>
>
>
>
> --
> Thanks & Regards,
> Brahmeswara Rao J.
>

>Thanks David for your quick response.

My name is NOT David!  It is Melvin.

>How do we get an exact number of rows in table.

As stated in the _documentation_, reltuples "This is only an estimate used
by the planner. It is updated by VACUUM, ANALYZE, and a few DDL commands
such as CREATE INDEX."

https://www.postgresql.org/docs/9.6/static/catalog-pg-class.html

Did you analyze your database before executing the query?
The reltuples are only updated during an analyze.

You need to do a
VACUUMDB -z -d yourdb_name


Also, in the future, please be kind enough to state your PostgreSQL VERSION
and O/S.


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

Re: Replica on low-bandwitdh network

[bricklen]

On Tue, Dec 5, 2017 at 1:12 AM, Job  wrote:

> we have some postgreswl 9.6.1 servers in two different continent.
> Latency is from 180 to 250 ms between the two sites.
>
> Actually we use Rubyrep for off-site replication, since servers are
> completely independent in case of connectivity loss.
>
> We are experiencing some problems with lock tables (rubyrep works with
> trigger to commit changes to the other server),
> we think for the uncommon latency.
>

​You might get more mileage from pglogical (
https://www.2ndquadrant.com/en/resources/pglogical/), which you can use to
ship just the changes, though given my unfamiliarity with Rubyrep maybe
that's what it does too (?)

Migrating From CentOS-6 Oracle 11g R2 To CentOS-7 PostgreSQL ??

[Eugene Poole]

I've asked most of my OS based questions on another mail list, but I 
think this is more specific:


On a physical machine I'm running CentOS-6 64-bit with Oracle 11gR2 and 
I want to migrate to another physical machine running CentOS-7 64-bit 
with PostgreSQL ??


   1. What version of PostgreSQL should I use?

   2. I don't want to use a RPM because I like controlling where
   software is installed, so where can I locate the required tar ball?

   3. Would I use ora2pg do do the move? Is ora2pg still maintained?

   4. Is there a better conversion package?

   5. What additional information might be helpful?

TIA

Gene

--
Eugene Poole
Woodstock, Georgia

RE: Migrating From CentOS-6 Oracle 11g R2 To CentOS-7 PostgreSQL ??

[Kevin Brannen]

From: Eugene Poole [mailto:etpool...@comcast.net]
Sent: Tuesday, December 05, 2017 12:59 PM
To: pgsql-gene...@postgresql.org
Subject: Migrating From CentOS-6 Oracle 11g R2 To CentOS-7 PostgreSQL ??


I've asked most of my OS based questions on another mail list, but I think this 
is more specific:

On a physical machine I'm running CentOS-6 64-bit with Oracle 11gR2 and I want 
to migrate to another physical machine running CentOS-7 64-bit with PostgreSQL 
??

1. What version of PostgreSQL should I use?

The latest that works for you. Some people don’t like .0 releases for various 
reasons and if that applies to you, then use the latest of 9.6.x (9.6.5 I 
think); else use 10.0.

2. I don't want to use a RPM because I like controlling where software is 
installed, so where can I locate the required tar ball?

You can download the source and build it yourself; there’s an arg for where you 
want to install it. The instructions are pretty easy to follow if you’ve ever 
built anything.

3. Would I use ora2pg do do the move? Is ora2pg still maintained?

4. Is there a better conversion package?

5. What additional information might be helpful?

I find building & testing on a VM to be useful, as I can snapshop every step of 
the way and revert if required. I’ve never done an Oracle -> Pg conversion, but 
it’s been discussed on the list so you might want to search the archives for 
advice. In fact the last time wasn’t all that long ago, maybe a month?

HTH, Kevin

TIA

Gene

--

Eugene Poole

Woodstock, Georgia

This e-mail transmission, and any documents, files or previous e-mail messages 
attached to it, may contain confidential information. If you are not the 
intended recipient, or a person responsible for delivering it to the intended 
recipient, you are hereby notified that any disclosure, distribution, review, 
copy or use of any of the information contained in or attached to this message 
is STRICTLY PROHIBITED. If you have received this transmission in error, please 
immediately notify us by reply e-mail, and destroy the original transmission 
and its attachments without reading them or saving them to disk. Thank you.

Feature idea: Dynamic Data Making

[Riccardo Bassani]

As SQL Server 2016 (
https://docs.microsoft.com/en-us/sql/relational-databases/security/dynamic-data-masking
) , or Oracle does: it could be a neat feature having Dynamic Data
Masking, even if it has some limitations, it could help in the
application development, imho.

I know it's possible to put this logic in the application (if you look
in internet for "spring framework data masking" you find a lot of
results).

What do you think?
I know there is more important things to do (like partitioning or
parallelism), but it could be neat to see that in a future release .

Thank you,
Riccardo

Re: Feature idea: Dynamic Data Making

[Alvaro Herrera]

Riccardo Bassani wrote:
> As SQL Server 2016 (
> https://docs.microsoft.com/en-us/sql/relational-databases/security/dynamic-data-masking
> ) , or Oracle does: it could be a neat feature having Dynamic Data
> Masking, even if it has some limitations, it could help in the
> application development, imho.
> 
> I know it's possible to put this logic in the application (if you look
> in internet for "spring framework data masking" you find a lot of
> results).
> 
> What do you think?
> I know there is more important things to do (like partitioning or
> parallelism), but it could be neat to see that in a future release .

Data masking is pretty important actually.  Since you seem to have an
interest in seeing it done, care to submit a patch?

-- 
Álvaro Herrerahttps://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Re: Feature idea: Dynamic Data Making

[Karsten Hilbert]

On Tue, Dec 05, 2017 at 09:59:22PM +0100, Riccardo Bassani wrote:

> https://docs.microsoft.com/en-us/sql/relational-databases/security/dynamic-data-masking

There's already at least 4 mechanismus that come to mind
which can do things like that:

on SELECT RULEs

VIEWs

column GRANTs

RLS

Karsten
-- 
GPG key ID E4071346 @ eu.pool.sks-keyservers.net
E167 67FD A291 2BEA 73BD  4537 78B9 A9F9 E407 1346

Re: Feature idea: Dynamic Data Making

[Riccardo Bassani]

Sorry, I don't develop in C.
I could always learn how to develop in C, and how it works a DBMS, but
it will take some time :)

Anyway it's interisting to see some some people interested in that feature, too.

2017-12-05 22:03 GMT+01:00 Alvaro Herrera :
> Riccardo Bassani wrote:
>> As SQL Server 2016 (
>> https://docs.microsoft.com/en-us/sql/relational-databases/security/dynamic-data-masking
>> ) , or Oracle does: it could be a neat feature having Dynamic Data
>> Masking, even if it has some limitations, it could help in the
>> application development, imho.
>>
>> I know it's possible to put this logic in the application (if you look
>> in internet for "spring framework data masking" you find a lot of
>> results).
>>
>> What do you think?
>> I know there is more important things to do (like partitioning or
>> parallelism), but it could be neat to see that in a future release .
>
> Data masking is pretty important actually.  Since you seem to have an
> interest in seeing it done, care to submit a patch?
>
> --
> Álvaro Herrerahttps://www.2ndQuadrant.com/
> PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Re: Feature idea: Dynamic Data Making

[Riccardo Bassani]

I will have a look, thank you for the tips!

2017-12-05 22:11 GMT+01:00 Karsten Hilbert :
> On Tue, Dec 05, 2017 at 09:59:22PM +0100, Riccardo Bassani wrote:
>
>> https://docs.microsoft.com/en-us/sql/relational-databases/security/dynamic-data-masking
>
> There's already at least 4 mechanismus that come to mind
> which can do things like that:
>
> on SELECT RULEs
>
> VIEWs
>
> column GRANTs
>
> RLS
>
> Karsten
> --
> GPG key ID E4071346 @ eu.pool.sks-keyservers.net
> E167 67FD A291 2BEA 73BD  4537 78B9 A9F9 E407 1346
>

Re: Feature idea: Dynamic Data Making

[John R Pierce]

On 12/5/2017 1:11 PM, Karsten Hilbert wrote:

On Tue, Dec 05, 2017 at 09:59:22PM +0100, Riccardo Bassani wrote:


https://docs.microsoft.com/en-us/sql/relational-databases/security/dynamic-data-masking

There's already at least 4 mechanismus that come to mind
which can do things like that:

on SELECT RULEs

VIEWs

column GRANTs

RLS


yeah, but its so much cooler if you invent a bunch of proprietary 
methods of doing the same thing, and give it new marketing buzzwords !!!







--
john r pierce, recycling bits in santa cruz

a back up question

[Martin Mueller]

Are there rules for thumb for deciding when you can dump a whole database and 
when you’d be better off dumping groups of tables? I have a database that has 
around 100 tables, some of them quite large, and right now the data directory 
is well over 100GB. My hunch is that I should divide and conquer, but I don’t 
have a clear sense of what counts as  “too big” these days. Nor do I have a 
clear sense of whether the constraints have to do with overall size, the number 
of tables, or machine memory (my machine has 32GB of memory).

Is 10GB a good practical limit to keep in mind?

Re: a back up question

[David G. Johnston]

On Tue, Dec 5, 2017 at 2:52 PM, Martin Mueller <
martinmuel...@northwestern.edu> wrote:

> Are there rules for thumb for deciding when you can dump a whole database
> and when you’d be better off dumping groups of tables? I have a database
> that has around 100 tables, some of them quite large, and right now the
> data directory is well over 100GB. My hunch is that I should divide and
> conquer, but I don’t have a clear sense of what counts as  “too big” these
> days. Nor do I have a clear sense of whether the constraints have to do
> with overall size, the number of tables, or machine memory (my machine has
> 32GB of memory).
>
>
>
> Is 10GB a good practical limit to keep in mind?
>
>
>
​I'd say the rule-of-thumb is if you have to "divide-and-conquer" you
should use non-pg_dump based backup solutions.  Too big is usually measured
in units of time, not memory.​

Any ability to partition your backups into discrete chunks is going to be
very specific to your personal setup.  Restoring such a monster without
constraint violations is something I'd be VERY worried about.

David J.

Re: a back up question

[Carl Karsten]

Nothing wrong with lots of tables and data.

Don't impose any constraints on your problem you don't need to.

Like what are you backing up to?$400 for a 1T ssd or $80 fo a 2T usb3
spinny disk.

If you are backing up while the db is being updated, you need to make sure
updates are queued until the backup is done.  don't mess with that
process.   personally I would assume the db is always being updated and
expect that.




On Tue, Dec 5, 2017 at 3:52 PM, Martin Mueller <
martinmuel...@northwestern.edu> wrote:

> Are there rules for thumb for deciding when you can dump a whole database
> and when you’d be better off dumping groups of tables? I have a database
> that has around 100 tables, some of them quite large, and right now the
> data directory is well over 100GB. My hunch is that I should divide and
> conquer, but I don’t have a clear sense of what counts as  “too big” these
> days. Nor do I have a clear sense of whether the constraints have to do
> with overall size, the number of tables, or machine memory (my machine has
> 32GB of memory).
>
>
>
> Is 10GB a good practical limit to keep in mind?
>
>
>
>
>



-- 
Carl K

Re: a back up question

[Martin Mueller]

Time is not really a problem for me, if we talk about hours rather than days.  
On a roughly comparable machine I’ve made backups of databases less than 10 GB, 
and it was a matter of minutes.  But I know that there are scale problems. 
Sometimes programs just hang if the data are beyond some size.  Is that likely 
in Postgres if you go from ~ 10 GB to ~100 GB?  There isn’t any interdependence 
among my tables beyond  queries I construct on the fly, because I use the 
database in a single user environment

From: "David G. Johnston" 
Date: Tuesday, December 5, 2017 at 3:59 PM
To: Martin Mueller 
Cc: "pgsql-general@lists.postgresql.org" 
Subject: Re: a back up question

On Tue, Dec 5, 2017 at 2:52 PM, Martin Mueller 
mailto:martinmuel...@northwestern.edu>> wrote:
Are there rules for thumb for deciding when you can dump a whole database and 
when you’d be better off dumping groups of tables? I have a database that has 
around 100 tables, some of them quite large, and right now the data directory 
is well over 100GB. My hunch is that I should divide and conquer, but I don’t 
have a clear sense of what counts as  “too big” these days. Nor do I have a 
clear sense of whether the constraints have to do with overall size, the number 
of tables, or machine memory (my machine has 32GB of memory).

Is 10GB a good practical limit to keep in mind?


​I'd say the rule-of-thumb is if you have to "divide-and-conquer" you should 
use non-pg_dump based backup solutions.  Too big is usually measured in units 
of time, not memory.​

Any ability to partition your backups into discrete chunks is going to be very 
specific to your personal setup.  Restoring such a monster without constraint 
violations is something I'd be VERY worried about.

David J.

Re: a back up question

[Alvaro Herrera]

Carl Karsten wrote:
> Nothing wrong with lots of tables and data.
> 
> Don't impose any constraints on your problem you don't need to.
> 
> Like what are you backing up to?$400 for a 1T ssd or $80 fo a 2T usb3
> spinny disk.
> 
> If you are backing up while the db is being updated, you need to make sure
> updates are queued until the backup is done.  don't mess with that
> process.   personally I would assume the db is always being updated and
> expect that.

A backup generated by pg_dump never includes writes that are in flight
while the backup is being taken.  That would make the backup absolutely
worthless!

-- 
Álvaro Herrerahttps://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Re: a back up question

[David G. Johnston]

On Tue, Dec 5, 2017 at 3:09 PM, Martin Mueller <
martinmuel...@northwestern.edu> wrote:

> Time is not really a problem for me, if we talk about hours rather than
> days.  On a roughly comparable machine I’ve made backups of databases less
> than 10 GB, and it was a matter of minutes.  But I know that there are
> scale problems. Sometimes programs just hang if the data are beyond some
> size.  Is that likely in Postgres if you go from ~ 10 GB to ~100 GB?  There
> isn’t any interdependence among my tables beyond  queries I construct on
> the fly, because I use the database in a single user environment
>
>
>
The convention on these lists is to inline and/or bottom-post​; please
avoid top-posting.

That you are using a relational database system to house tables without any
interdependence (relationships) between them is an interesting
proposition.  That you are in a "single user environment" in most cases
would have no impact on this...

PostgreSQL itself, bugs not withstanding, won't "hang" no matter how much
data is being processed.  It does, however, take out locks so that the
entire dump represents that exact same snapshot for all dumped objects.
Those locks can impact queries.  In particular using "TRUNCATE" becomes
pretty much impossible while a dump backup is in progress (I get bit by
this, I tend to truncate unlogged tables quite a bit in my usage of
PostgreSQL).  Normal updates and selects usually work without problem
though any transactions started after the backup will not be part of the
output no matter how long after the transaction closes the backup finishes.

I suspect that typically you will end up annoyed at how long the backup
takes well before any program/system issues become apparent.  Data is
streamed to the output file handle so active memory usage and database size
are not really correlated.

David J.

Re: a back up question

[Carl Karsten]

On Tue, Dec 5, 2017 at 4:15 PM, Alvaro Herrera 
wrote:

> Carl Karsten wrote:
> > Nothing wrong with lots of tables and data.
> >
> > Don't impose any constraints on your problem you don't need to.
> >
> > Like what are you backing up to?$400 for a 1T ssd or $80 fo a 2T usb3
> > spinny disk.
> >
> > If you are backing up while the db is being updated, you need to make
> sure
> > updates are queued until the backup is done.  don't mess with that
> > process.   personally I would assume the db is always being updated and
> > expect that.
>
> A backup generated by pg_dump never includes writes that are in flight
> while the backup is being taken.  That would make the backup absolutely
> worthless!
>

Hmm, i kinda glossed over my point:
if you come up with your own process to chop up the backup into little
pieces, you risk letting writes in, and then yeah, worthless.



-- 
Carl K

Re: a back up question

[John R Pierce]

On 12/5/2017 2:09 PM, Martin Mueller wrote:
Time is not really a problem for me, if we talk about hours rather 
than days.  On a roughly comparable machine I’ve made backups of 
databases less than 10 GB, and it was a matter of minutes.  But I know 
that there are scale problems. Sometimes programs just hang if the 
data are beyond some size.  Is that likely in Postgres if you go from 
~ 10 GB to ~100 GB?  There isn’t any interdependence among my tables 
beyond  queries I construct on the fly, because I use the database in 
a single user environment


another factor is restore time.    restores have to create indexes.   
creating indexes on multi-million-row tables can take awhile.  (hint, be 
sure to set maintenance_work_mem to 1GB before doing this!)




--
john r pierce, recycling bits in santa cruz

Re: a back up question

[Alvaro Herrera]

Carl Karsten wrote:
> On Tue, Dec 5, 2017 at 4:15 PM, Alvaro Herrera 
> wrote:

> > A backup generated by pg_dump never includes writes that are in flight
> > while the backup is being taken.  That would make the backup absolutely
> > worthless!
> 
> Hmm, i kinda glossed over my point:
> if you come up with your own process to chop up the backup into little
> pieces, you risk letting writes in, and then yeah, worthless.

Ah, sure.

-- 
Álvaro Herrerahttps://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

RE: Replica on low-bandwitdh network

[Ahmed, Nawaz]

You might as well try Slony.

Best Regards,

Nawaz Ahmed




-Original Message-
From: Job [mailto:j...@colliniconsulting.it]
Sent: Tuesday, 5 December 2017 8:12 PM
To: pgsql-gene...@postgresql.org
Subject: Replica on low-bandwitdh network

Hi guys,

we have some postgreswl 9.6.1 servers in two different continent.
Latency is from 180 to 250 ms between the two sites.

Actually we use Rubyrep for off-site replication, since servers are completely 
independent in case of connectivity loss.

We are experiencing some problems with lock tables (rubyrep works with trigger 
to commit changes to the other server), we think for the uncommon latency.

Which is, for you, the best replication module for Postgresql?
Bucardo? Something else?

Thank you, very best!
F

Disclaimer

The information in this e-mail is confidential and may contain content that is 
subject to copyright and/or is commercial-in-confidence and is intended only 
for the use of the above named addressee. If you are not the intended 
recipient, you are hereby notified that dissemination, copying or use of the 
information is strictly prohibited. If you have received this e-mail in error, 
please telephone Fujitsu Australia Software Technology Pty Ltd on + 61 2 9452 
9000 or by reply e-mail to the sender and delete the document and all copies 
thereof.


Whereas Fujitsu Australia Software Technology Pty Ltd would not knowingly 
transmit a virus within an email communication, it is the receiver’s 
responsibility to scan all communication and any files attached for computer 
viruses and other defects. Fujitsu Australia Software Technology Pty Ltd does 
not accept liability for any loss or damage (whether direct, indirect, 
consequential or economic) however caused, and whether by negligence or 
otherwise, which may result directly or indirectly from this communication or 
any files attached.


If you do not wish to receive commercial and/or marketing email messages from 
Fujitsu Australia Software Technology Pty Ltd, please email 
unsubscr...@fast.au.fujitsu.com

Re: Partition pruning / agg push down for star schema in pg v11

[legrand legrand]

Partition pruning doen't work in this case
as described at
http://www.postgresql-archive.org/Partition-pruning-for-Star-Schema-td5995163.html#a5995168

Partition wise join works for ONLY ONE dim table (that is better than
nothing).



--
Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html

Re: transaction wrap around

[Thomas Munro]

On Tue, Dec 5, 2017 at 5:43 PM, Jeff Janes  wrote:
> On Mon, Dec 4, 2017 at 5:52 PM, John R Pierce  wrote:
>> On 12/4/2017 2:21 PM, chris kim wrote:
>>> How would I investigate if my database is nearing a transaction wrap
>>> around.
>>
>> it would be screaming bloody murder in the log, for one.
>>
>
> Unfortunately, that comes far too late to repair the problem without a
> substantial service interruption, on very high transaction throughput
> installations.
>
> Also, people usually consult the logs to figure out what the problem is,
> once they become aware that one exists.  That is also too late.

The problem is that our logic (1) focuses on when we should *start*
freezing, not by when we'd like to be finished, and (2) is defined in
such a way that many tables are likely to reach the trigger point at
the same time.  Even if your system can handle the load, you might not
like the disruption to regular vacuuming and analyze work.

An ideal system would estimate how long it's going to take and how
long we've got (current tx consumption rate, xids remaining) before
autovacuum_freeze_max_age  is reached and then spread the work out so
that we get it done just in time with minimal impact.  Getting
reliable estimates to control that seems hard though.

Perhaps we could add a much simpler first defence that tries to
prevent autovacuum_freeze_max_age (and its multixact cousin) from
being reached like this: consider launching at most one wraparound
vacuum for any relation that is *half way* to
autovacuum_freeze_max_age.  That gives the system a chance to handle
each partition of a monster partitioned table calmly in series even if
they have the same age dating back to schema creation/data load time.
Perhaps it could consider adding more vacuum backends as you approach
autovacuum_freeze_max_age, or something.  Hopefully you'd never
actually reach it.

Of course you can do what I just said with a cron job, and there may
be better heuristics than that, but it'd be nice to find *some* way to
make freeze max age more gradual by default on large databases, until
such time as we can kill it with 64 bit xids or other major efforts.
My understanding is that even with the new freeze map, most big
systems will still pay the full price for the first wraparound vacuum
freeze, so I still expect to encounter 20TB production databases in
the wild that have gone into a wraparound frenzy confounding their
owners.

-- 
Thomas Munro
http://www.enterprisedb.com

Re: Replica on low-bandwitdh network

[Ben Madin]

I don't know if this is relevant, but we use the default streaming
replication and were not always keeping up. Now we WAL log ship to S3
bucket... seems to work pretty well... cheers Ben

On 5 December 2017 at 17:12, Job  wrote:

> Hi guys,
>
> we have some postgreswl 9.6.1 servers in two different continent.
> Latency is from 180 to 250 ms between the two sites.
>
> Actually we use Rubyrep for off-site replication, since servers are
> completely independent in case of connectivity loss.
>
> We are experiencing some problems with lock tables (rubyrep works with
> trigger to commit changes to the other server),
> we think for the uncommon latency.
>
> Which is, for you, the best replication module for Postgresql?
> Bucardo? Something else?
>
> Thank you, very best!
> F
>
>


-- 

Dr Ben Madin



m : +61 448 887 220

e : b...@ausvet.com.au

5 Shuffrey Street, Fremantle
Western Australia

on the web: www.ausvet.com.au


This transmission is for the intended addressee only and is confidential
information. If you have received this transmission in error, please delete
it and notify the sender. The contents of this email are the opinion of the
writer only and are not endorsed by Ausvet unless expressly stated
otherwise. Although Ausvet uses virus scanning software we do not accept
liability for viruses or similar in any attachments.