Re: Oracle to postgres migration

[Vasilis Ventirozos]

I'd start with this first : https://ora2pg.darold.net 


> On 26 Dec 2017, at 02:30, Azimuddin Mohammed  wrote:
> 
> Hello,
> Can anyone guide me through the steps for migration from oracle to postgres 
> with config changes required, keeping in mind that neither I am a oracle DBA 
> not postgres admin
> 
> -- 
> 
> Regards,
> Azim
> 
> 
>  
> 
>   Virus-free. www.avast.com 
> 
>  

[table partitioning] How many partitions are possibel?

[Michelle Konzack]

Hello *,

Long time ago I used VIEWs on my history database and when PostgreSQL
introduced table partitioning I was switching to it.

Currently I use Debian GNU/Linux 7.11 with PostgreSQL 9.1 (table space
and table partitioning) using an Adaptec 16-Channel Raid-1 Controller
with 16x 6 TByte SAS UltraStar (HGST) drives.

I created partitions of 100 years range and have currently 132 of them.

There are 8 base columns (1 data colum in english) and then for each
language used an additional colum. Some of the data where translated
into 27 languages but I have 56 non-english columns.

So in total I have currently 64 columns and arround 156mio rows.

Th etables are organised into

history_earth
history_earth_021_a
history_earth_020_a
history_earth_019_a
...
history_earth_124_b
...

"a" mean A.C. and "b" B.C.

While checking my Root-Servers I discovered, that my PostgreSQL Box has
a huge performance problem, because some table partitions became VERY
large (3 table partitions exceed already 1 TByte and 14 are 500-1000GB).

I like to make the partitions smaller, but the documentation say, you
should not dare to make several 1000 partitions..

Question:

What is with is today possibel with table partitioning?

And there is another thing:

The 16 "new" SAS Drives where sponsored, hence free for me including the
new Raid-1 Controller, but I do not like the idea, to have a singel
physical database of arround 40 TByte diskspace...

I can get much less expensive (not cheaper) SAS drives of 1 or 2 TByte
and since the Raid-1 Controller cost only 400€, I can use several LOW
POWER machines (Mini-ITX) to accomplish the task because the webinterface
is anyway stored on a seperated server.

Any suggestions?


-- 
Michelle Konzack
00372-54541400

Re: Oracle to postgres migration

[Timo Myyrä]

On Tue, Dec 26, 2017, at 11:36, Vasilis Ventirozos wrote:
> I'd start with this first : https://ora2pg.darold.net
> 
>> On 26 Dec 2017, at 02:30, Azimuddin Mohammed
>>  wrote:>> 
>> Hello,
>> Can anyone guide me through the steps for migration from oracle to
>> postgres with config changes required, keeping in mind that neither I
>> am a oracle DBA not postgres admin>> 
>> -- 
>> 
>> Regards,
>> Azim
>> 
>>  
>> Virus-free. www.avast.com[1]

I second the ora2pg tool. Currently working on oracle to postgresql
migration with it and ora2pg helps a lot.But you can't get ready recipe for 
migration. There's ton of stuff to
consider from trivial rewriting NVL queries to use COALESCE to more
bigger stuff like lack of synonyms in postgresql. All depends on your
current Oracle database.
Timo

Links:

  1. 
https://www.avast.com/sig-email?utm_medium=email&utm_source=link&utm_campaign=sig-email&utm_content=webmail&utm_term=link

Does PostgreSQL check database integrity at startup?

[Edson Carlos Ericksson Richter]

Recently I had a problem with a base file with size 0 in a standby server.

This raised one question: does PostgreSQL (9.6.6) check base integrity 
at startup?


At least if there are 0 byte size files in base dir? Or CRC? Something?


Regards,

--

*Edson Carlos Ericksson Richter*
/SimKorp Ltda/
Fone:   (51) 3366-7964
Embedded Image
/"A mente que se abre a uma nova ideia jamais voltará ao seu tamanho 
original"/

- Albert Einstein

Re: Oracle to postgres migration

[Timo Myyrä]

On Tue, Dec 26, 2017, at 02:30, Azimuddin Mohammed wrote:
> Hello,
> Can anyone guide me through the steps for migration from oracle to
> postgres with config changes required, keeping in mind that neither I
> am a oracle DBA not postgres admin> 
> -- 
> 
> Regards,
> Azim
> 
>  
> Virus-free. www.avast.com[1]

You should read this through before starting the migration process. 
https://ora2pg.darold.net/slides/ora2pg_the_hard_way.pdf

Timo

Links:

  1. 
https://www.avast.com/sig-email?utm_medium=email&utm_source=link&utm_campaign=sig-email&utm_content=webmail&utm_term=link

Re: Does PostgreSQL check database integrity at startup?

[Martin Marques]

El 26/12/17 a las 09:52, Edson Carlos Ericksson Richter escribió:
> Recently I had a problem with a base file with size 0 in a standby server.
> 
> This raised one question: does PostgreSQL (9.6.6) check base integrity
> at startup?
> 
> At least if there are 0 byte size files in base dir? Or CRC? Something?

Yes it has CRC check, but only if you initialize the cluster with
--data-checksums, and there's a price to pay in performance.


-- 
Martín Marquéshttp://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

Re: Does PostgreSQL check database integrity at startup?

[Pavel Stehule]

2017-12-26 14:44 GMT+01:00 Martin Marques :

> El 26/12/17 a las 09:52, Edson Carlos Ericksson Richter escribió:
> > Recently I had a problem with a base file with size 0 in a standby
> server.
> >
> > This raised one question: does PostgreSQL (9.6.6) check base integrity
> > at startup?
> >
> > At least if there are 0 byte size files in base dir? Or CRC? Something?
>
> Yes it has CRC check, but only if you initialize the cluster with
> --data-checksums, and there's a price to pay in performance.
>
>
It has CRC check, but it is used in runtime - when data are necessary

So Postgres usually check nothing on start - few system tables and indexes

Regards

Pavel

>
> --
> Martín Marquéshttp://www.2ndQuadrant.com/
> PostgreSQL Development, 24x7 Support, Training & Services
>
>

Re: Does PostgreSQL check database integrity at startup?

[Edson Carlos Ericksson Richter]

Em 26/12/2017 12:25, Pavel Stehule escreveu:



2017-12-26 14:44 GMT+01:00 Martin Marques 
mailto:martin.marq...@2ndquadrant.com>>:


El 26/12/17 a las 09:52, Edson Carlos Ericksson Richter escribió:
> Recently I had a problem with a base file with size 0 in a
standby server.
>
> This raised one question: does PostgreSQL (9.6.6) check base
integrity
> at startup?
>
> At least if there are 0 byte size files in base dir? Or CRC?
Something?

Yes it has CRC check, but only if you initialize the cluster with
--data-checksums, and there's a price to pay in performance.


It has CRC check, but it is used in runtime - when data are necessary

So Postgres usually check nothing on start - few system tables and indexes

Regards

Pavel


--
Martín Marqués http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services




Any tips to make database server don't start if corrupt?
If I can change the startup script to make some checks before 
effectively starting the database, what would be the recommendations?


One that seems obvious to me are empty data files (something like "find 
-size 0 $PG_DATA/base")...
But I'm sure that more experienced PostgreSQL DBA would have more tests 
to check before startup.


Thanks,

Edson

Re: Does PostgreSQL check database integrity at startup?

[Peter Geoghegan]

Look into amcheck:

https://github.com/petergeoghegan/amcheck

--
Peter Geoghegan
(Sent from my phone)

Re: Does PostgreSQL check database integrity at startup?

[Pavel Stehule]

2017-12-26 16:37 GMT+01:00 Edson Carlos Ericksson Richter <
rich...@simkorp.com.br>:

> Em 26/12/2017 12:25, Pavel Stehule escreveu:
>
>
>
> 2017-12-26 14:44 GMT+01:00 Martin Marques 
> :
>
>> El 26/12/17 a las 09:52, Edson Carlos Ericksson Richter escribió:
>> > Recently I had a problem with a base file with size 0 in a standby
>> server.
>> >
>> > This raised one question: does PostgreSQL (9.6.6) check base integrity
>> > at startup?
>> >
>> > At least if there are 0 byte size files in base dir? Or CRC? Something?
>>
>> Yes it has CRC check, but only if you initialize the cluster with
>> --data-checksums, and there's a price to pay in performance.
>>
>>
> It has CRC check, but it is used in runtime - when data are necessary
>
> So Postgres usually check nothing on start - few system tables and indexes
>
> Regards
>
> Pavel
>
>>
>> --
>> Martín Marquéshttp://www.2ndQuadrant.com/
>> PostgreSQL Development, 24x7 Support, Training & Services
>>
>>
>
> Any tips to make database server don't start if corrupt?
> If I can change the startup script to make some checks before effectively
> starting the database, what would be the recommendations?
>
> One that seems obvious to me are empty data files (something like "find
> -size 0 $PG_DATA/base")...
> But I'm sure that more experienced PostgreSQL DBA would have more tests to
> check before startup.
>

I don't think so anybody does it. Reading 1TB database needs more then few
hours.

Regards



>
> Thanks,
>
> Edson
>

Re: Does PostgreSQL check database integrity at startup?

[Edson Carlos Ericksson Richter]

Em 26/12/2017 13:40, Pavel Stehule escreveu:



2017-12-26 16:37 GMT+01:00 Edson Carlos Ericksson Richter 
mailto:rich...@simkorp.com.br>>:


Em 26/12/2017 12:25, Pavel Stehule escreveu:



2017-12-26 14:44 GMT+01:00 Martin Marques
mailto:martin.marq...@2ndquadrant.com>>:

El 26/12/17 a las 09:52, Edson Carlos Ericksson Richter escribió:
> Recently I had a problem with a base file with size 0 in a
standby server.
>
> This raised one question: does PostgreSQL (9.6.6) check
base integrity
> at startup?
>
> At least if there are 0 byte size files in base dir? Or
CRC? Something?

Yes it has CRC check, but only if you initialize the cluster with
--data-checksums, and there's a price to pay in performance.


It has CRC check, but it is used in runtime - when data are
necessary

So Postgres usually check nothing on start - few system tables
and indexes

Regards

Pavel


--
Martín Marqués http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services




Any tips to make database server don't start if corrupt?
If I can change the startup script to make some checks before
effectively starting the database, what would be the recommendations?

One that seems obvious to me are empty data files (something like
"find -size 0 $PG_DATA/base")...
But I'm sure that more experienced PostgreSQL DBA would have more
tests to check before startup.


I don't think so anybody does it. Reading 1TB database needs more then 
few hours.


Regards



Thanks,

Edson


I'm rebuilding the standby server for two days already, with 23% of 
completion status...
If lost the database and backups because of that failure, it would be a 
giant disaster.
Few hours checking integrity would be acceptable... Specially if I can 
run it on standby only.


Regards,

Edson

Re: Does PostgreSQL check database integrity at startup?

[Pavel Stehule]

2017-12-26 16:50 GMT+01:00 Edson Carlos Ericksson Richter <
rich...@simkorp.com.br>:

> Em 26/12/2017 13:40, Pavel Stehule escreveu:
>
>
>
> 2017-12-26 16:37 GMT+01:00 Edson Carlos Ericksson Richter <
> rich...@simkorp.com.br>:
>
>> Em 26/12/2017 12:25, Pavel Stehule escreveu:
>>
>>
>>
>> 2017-12-26 14:44 GMT+01:00 Martin Marques > >:
>>
>>> El 26/12/17 a las 09:52, Edson Carlos Ericksson Richter escribió:
>>> > Recently I had a problem with a base file with size 0 in a standby
>>> server.
>>> >
>>> > This raised one question: does PostgreSQL (9.6.6) check base integrity
>>> > at startup?
>>> >
>>> > At least if there are 0 byte size files in base dir? Or CRC? Something?
>>>
>>> Yes it has CRC check, but only if you initialize the cluster with
>>> --data-checksums, and there's a price to pay in performance.
>>>
>>>
>> It has CRC check, but it is used in runtime - when data are necessary
>>
>> So Postgres usually check nothing on start - few system tables and indexes
>>
>> Regards
>>
>> Pavel
>>
>>>
>>> --
>>> Martín Marquéshttp://www.2ndQuadrant.com/
>>> PostgreSQL Development, 24x7 Support, Training & Services
>>>
>>>
>>
>> Any tips to make database server don't start if corrupt?
>> If I can change the startup script to make some checks before effectively
>> starting the database, what would be the recommendations?
>>
>> One that seems obvious to me are empty data files (something like "find
>> -size 0 $PG_DATA/base")...
>> But I'm sure that more experienced PostgreSQL DBA would have more tests
>> to check before startup.
>>
>
> I don't think so anybody does it. Reading 1TB database needs more then few
> hours.
>
> Regards
>
>
>
>>
>> Thanks,
>>
>> Edson
>>
>
> I'm rebuilding the standby server for two days already, with 23% of
> completion status...
> If lost the database and backups because of that failure, it would be a
> giant disaster.
> Few hours checking integrity would be acceptable... Specially if I can run
> it on standby only.
>

very simple check

pgdumpall > /dev/null

but this doesn't check indexes.

Regards

Pavel




> Regards,
>
> Edson
>

Re: Deadlock between concurrent index builds on different tables

[Alvaro Herrera]

Jeremy Finzel wrote:
> >
> >
> >> Each index build needs to wait for all other transactions
> >> (Including the ones used by the other index build) to finish.
> >> So I don't think a deadlock here is unexpected.

> Does that mean I should never build more than one concurrent index at
> a time within the entire cluster? If so, that is not clear from the
> documentation.

No, there is no such expectation.  Jeff analyzed your scenario,
discovered a bug and sent a patch to fix it -- care to test it and
report back?  You can get it from here:

https://www.postgresql.org/message-id/CAMkU=1ztk3tpqdcunbxq93pc80frxujpdwlgmevbdx71ghn...@mail.gmail.com

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

Re: Deadlock between concurrent index builds on different tables

[Jeremy Finzel]

On Tue, Dec 26, 2017 at 10:28 AM, Alvaro Herrera 
wrote:

> Jeremy Finzel wrote:
> > >
> > >
> > >> Each index build needs to wait for all other transactions
> > >> (Including the ones used by the other index build) to finish.
> > >> So I don't think a deadlock here is unexpected.
>
> > Does that mean I should never build more than one concurrent index at
> > a time within the entire cluster? If so, that is not clear from the
> > documentation.
>
> No, there is no such expectation.  Jeff analyzed your scenario,
> discovered a bug and sent a patch to fix it -- care to test it and
> report back?  You can get it from here:
>
> https://www.postgresql.org/message-id/CAMkU=1ztk3TpQdcUNbxq93pc80FrXUjpDWL
> gmevbdx71ghn...@mail.gmail.com
>
> --
> Álvaro Herrerahttps://www.2ndQuadrant.com/
> PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
>
>
I would be thrilled to review it, but I need a little direction as I have
not done a patch review before.  I have been reading through some of the
developer FAQ and patch info. The attached file is simply a git diff, so
I'm not sure how I am to use this?  Is there a specific source version I
can download and compile?  I know where to get the current master, etc.,
from git, but where can I get the patched version or what is the proper way
to apply the patch to current master?

Thanks!
Jeremy

Re: Does PostgreSQL check database integrity at startup?

[Edson Carlos Ericksson Richter]

Em 26/12/2017 13:57, Pavel Stehule escreveu:



2017-12-26 16:50 GMT+01:00 Edson Carlos Ericksson Richter 
mailto:rich...@simkorp.com.br>>:


Em 26/12/2017 13:40, Pavel Stehule escreveu:



2017-12-26 16:37 GMT+01:00 Edson Carlos Ericksson Richter
mailto:rich...@simkorp.com.br>>:

Em 26/12/2017 12:25, Pavel Stehule escreveu:



2017-12-26 14:44 GMT+01:00 Martin Marques
mailto:martin.marq...@2ndquadrant.com>>:

El 26/12/17 a las 09:52, Edson Carlos Ericksson Richter
escribió:
> Recently I had a problem with a base file with size 0
in a standby server.
>
> This raised one question: does PostgreSQL (9.6.6)
check base integrity
> at startup?
>
> At least if there are 0 byte size files in base dir?
Or CRC? Something?

Yes it has CRC check, but only if you initialize the
cluster with
--data-checksums, and there's a price to pay in performance.


It has CRC check, but it is used in runtime - when data are
necessary

So Postgres usually check nothing on start - few system
tables and indexes

Regards

Pavel


--
Martín Marqués http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services




Any tips to make database server don't start if corrupt?
If I can change the startup script to make some checks before
effectively starting the database, what would be the
recommendations?

One that seems obvious to me are empty data files (something
like "find -size 0 $PG_DATA/base")...
But I'm sure that more experienced PostgreSQL DBA would have
more tests to check before startup.


I don't think so anybody does it. Reading 1TB database needs more
then few hours.

Regards



Thanks,

Edson



I'm rebuilding the standby server for two days already, with 23%
of completion status...
If lost the database and backups because of that failure, it would
be a giant disaster.
Few hours checking integrity would be acceptable... Specially if I
can run it on standby only.


very simple check

pgdumpall > /dev/null

but this doesn't check indexes.

Regards

Pavel




I'll check it and make some tests.
Thanks!

Edson

Re: Deadlock between concurrent index builds on different tables

[Alvaro Herrera]

Jeremy Finzel wrote:
> On Tue, Dec 26, 2017 at 10:28 AM, Alvaro Herrera 
> wrote:
> 
> > Jeremy Finzel wrote:
> > > >
> > > >> Each index build needs to wait for all other transactions
> > > >> (Including the ones used by the other index build) to finish.
> > > >> So I don't think a deadlock here is unexpected.
> >
> > > Does that mean I should never build more than one concurrent index at
> > > a time within the entire cluster? If so, that is not clear from the
> > > documentation.
> >
> > No, there is no such expectation.  Jeff analyzed your scenario,
> > discovered a bug and sent a patch to fix it -- care to test it and
> > report back?  You can get it from here:
> >
> > https://www.postgresql.org/message-id/CAMkU=1ztk3TpQdcUNbxq93pc80FrXUjpDWL
> > gmevbdx71ghn...@mail.gmail.com

> I would be thrilled to review it, but I need a little direction as I have
> not done a patch review before.  I have been reading through some of the
> developer FAQ and patch info. The attached file is simply a git diff, so
> I'm not sure how I am to use this?  Is there a specific source version I
> can download and compile?  I know where to get the current master, etc.,
> from git, but where can I get the patched version or what is the proper way
> to apply the patch to current master?

I don't think a patch review as such is necessary -- that code is very
complex and you'd need to learn about a lot of internals (though I won't
stop if you want to learn).  I was thinking about testing it, instead.

To create a patched build,
1. get a clone with the branch you're on.  Assuming you're on 9.6, it'd
   be like this
   git clone  -b REL9_6_STABLE

2. apply the patch on top
   cd postgresql
   patch -p1 < /path/to/file.diff
   # you could use "git apply" instead (or "git am", but not with this one)

3. configure and make
   ./configure 
   make
   make install

4. run it
   initdb -D# to create a fresh datadir
   pg_ctl 

You may need additional packages (zlib devel, readline devel, others;
see https://www.postgresql.org/docs/9.6/static/installation.html)

For the options in step 3 you could use whatever your current server
has; use "pg_config --configure" to find these out.  You're gonna need
same flags if you want to use your existing data directory.

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

Re: Does PostgreSQL check database integrity at startup?

[Alvaro Herrera]

Edson Carlos Ericksson Richter wrote:

> I'm rebuilding the standby server for two days already, with 23% of
> completion status...

So how do you build your standbys, exactly?  Maybe there's a bug in your
procedure, rather than a bug in the software.

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

Re: Does PostgreSQL check database integrity at startup?

[Edson Carlos Ericksson Richter]

Em 26/12/2017 15:13, Alvaro Herrera escreveu:

Edson Carlos Ericksson Richter wrote:


I'm rebuilding the standby server for two days already, with 23% of
completion status...

So how do you build your standbys, exactly?  Maybe there's a bug in your
procedure, rather than a bug in the software.

Usually, or by "pg_basebackup" or by "rsync" as described in PostgreSQL 
Wiki.

Right now, I'm doing via pg_basebackup.
Nothing extraordinary.

When using rsync:

rsync -e "ssh -2 -C -p slave_ssh_port" --progress --partial -az 
/pgsql/9.6/master_data_folder/* 
superuser_name@$slave_host:/home/pgsql/9.6/slave_data_folder/ --exclude 
postmaster.pid --exclude postgresql.conf --exclude pg_log


When using pg_basebackup:

/usr/pgsql-9.6/bin/pg_basebackup -h master_host -p master_port -D 
/home/pgsql/9.6/master_data_folder -R -S slot_name -X stream -P -d " 
user=superuser_name password=superuser_pass sslmode=require "



Of course, this is inside a bash script with variables at right places 
to make script generic as needed to make it works with dozens of 
databases...


Regards,

Edson

Re: Does PostgreSQL check database integrity at startup?

[David Steele]

On 12/26/17 10:57 AM, Pavel Stehule wrote:
2017-12-26 16:50 GMT+01:00 Edson Carlos Ericksson Richter 



I'm rebuilding the standby server for two days already, with 23% of
completion status...
If lost the database and backups because of that failure, it would
be a giant disaster.
Few hours checking integrity would be acceptable... Specially if I
can run it on standby only.


very simple check

pgdumpall > /dev/null

but this doesn't check indexes.


pgBackRest will validate all page checksums (including indexes, etc.) in 
the cluster during backup.  Full backups check everything, 
incr/differential backups check only the files that have changed.


--
-David
da...@pgmasters.net

Re: Does PostgreSQL check database integrity at startup?

[Alvaro Herrera]

David Steele wrote:

> pgBackRest will validate all page checksums (including indexes, etc.) in the
> cluster during backup.  Full backups check everything, incr/differential
> backups check only the files that have changed.

If a table or index file is of zero length when backed up, as in the
described case, nothing will be checked, right?  I mean, there is
nothing externally indicating that the file ought to be of a different
size.  Am I wrong?  So Edson's situation here would not raise any red
flags.

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

Re: Does PostgreSQL check database integrity at startup?

[rob stone]

Hello,


On Tue, 2017-12-26 at 18:58 -0300, Alvaro Herrera wrote:Hello,
> David Steele wrote:
> 
> > pgBackRest will validate all page checksums (including indexes,
> > etc.) in the
> > cluster during backup.  Full backups check everything,
> > incr/differential
> > backups check only the files that have changed.
> 
> If a table or index file is of zero length when backed up, as in the
> described case, nothing will be checked, right?  I mean, there is
> nothing externally indicating that the file ought to be of a
> different
> size.  Am I wrong?  So Edson's situation here would not raise any red
> flags.
> 


Could the following occur:-
1) Your app. issues a BEGIN followed by an INSERT.
2) Postgres decides to open a new file in order to store the new row.
3) Your app. then does a ROLLBACK.

Wouldn't that leave you with a zero length file on disk?

There's no reason for Postgres to delete the file just because a
rollback was issued. All it has to do is clear the buffer in memory.

My 2 cents.

Rob 

Re: Does PostgreSQL check database integrity at startup?

[Martin Marques]

El 26/12/17 a las 14:46, Edson Carlos Ericksson Richter escribió:
> 
> Usually, or by "pg_basebackup" or by "rsync" as described in PostgreSQL
> Wiki.
> Right now, I'm doing via pg_basebackup.
> Nothing extraordinary.
> 
> When using rsync:
> 
> rsync -e "ssh -2 -C -p slave_ssh_port" --progress --partial -az
> /pgsql/9.6/master_data_folder/*
> superuser_name@$slave_host:/home/pgsql/9.6/slave_data_folder/ --exclude
> postmaster.pid --exclude postgresql.conf --exclude pg_log

You don't say so anywhere, but I suspect you run the rsync between a
pg_start_backup() and pg_stop_backup(), right?

That is the way it's described in the wiki.

Regards,

-- 
Martín Marquéshttp://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

Re: Deadlock between concurrent index builds on different tables

[Alban Hertroys]

> On 26 Dec 2017, at 18:11, Alvaro Herrera  wrote:

…

> 3. configure and make
>   ./configure 
>   make
>   make install

…

> For the options in step 3 you could use whatever your current server
> has; use "pg_config --configure" to find these out.  You're gonna need
> same flags if you want to use your existing data directory.

Does that mean that at step 3 one could issue this?:

./configure `pg_config —configure`

If I had the sources at hand, I'd try that myself, but I don't and getting 
those is frankly a bit of a hassle to just test out whether that works ;)

Alban Hertroys
--
If you can't see the forest for the trees,
cut the trees and you'll find there is no forest.

Re: Deadlock between concurrent index builds on different tables

[Michael Paquier]

On Wed, Dec 27, 2017 at 12:27:05AM +0100, Alban Hertroys wrote:
> 
>> On 26 Dec 2017, at 18:11, Alvaro Herrera  wrote:
> 
> …
> 
> > 3. configure and make
> >   ./configure 
> >   make
> >   make install
> 
> …
> 
> > For the options in step 3 you could use whatever your current server
> > has; use "pg_config --configure" to find these out.  You're gonna need
> > same flags if you want to use your existing data directory.
> 
> Does that mean that at step 3 one could issue this?:
> 
> ./configure `pg_config —configure`
> 
> If I had the sources at hand, I'd try that myself, but I don't and getting 
> those is frankly a bit of a hassle to just test out whether that works ;)

For this issue I don't think that you are going to care much about
dependencies with low-level libraries like SSL or such as the behavior
is in integrality linked with PostgreSQL internals and the physical
representation of how transactions are handled with system catalogs. In
short there is no need to be fancy :)
--
Michael


signature.asc
Description: PGP signature

Re: [table partitioning] How many partitions are possibel?

[Michael Paquier]

On Tue, Dec 26, 2017 at 11:04:55AM +0100, Michelle Konzack wrote:
> I like to make the partitions smaller, but the documentation say, you
> should not dare to make several 1000 partitions..

Even more than a hundred may be already risky in my opinion here. The
issue with a large number of partitioning using the pre-9.6 grammar
comes from the planning time which sky-rockets because the optimizer
uses a O(N^2) algorithm to consider all the partitions. The v10 grammar
does not take care of this planning problem as far as I recall, but v11
gets that better for partition-wise joins if my memory does not fail me.
--
Michael


signature.asc
Description: PGP signature

postgresql-10 for ubuntu-17.10 (artful)?

[Stuart McGraw]

Is there a repository for Postgresql-10 available at 
  http://apt.postgresql.org/pub/repos/apt
for Ubuntu-17.10 (artful)?  When I look at the dist/
subdirectory there, there seem to be repos for all the
other Ubuntu releases including an upcoming one (bionic) 
but not artful.

Am I looking in the wrong place?  (I am new to Ubuntu 
and Debian packaging.)

Re: Deadlock between concurrent index builds on different tables

[Jeremy Finzel]

On Tue, Dec 26, 2017 at 11:11 AM, Alvaro Herrera 
wrote:

> Jeremy Finzel wrote:
> > On Tue, Dec 26, 2017 at 10:28 AM, Alvaro Herrera <
> alvhe...@alvh.no-ip.org>
> > wrote:
> >
> > > Jeremy Finzel wrote:
> > > > >
> > > > >> Each index build needs to wait for all other transactions
> > > > >> (Including the ones used by the other index build) to finish.
> > > > >> So I don't think a deadlock here is unexpected.
> > >
> > > > Does that mean I should never build more than one concurrent index at
> > > > a time within the entire cluster? If so, that is not clear from the
> > > > documentation.
> > >
> > > No, there is no such expectation.  Jeff analyzed your scenario,
> > > discovered a bug and sent a patch to fix it -- care to test it and
> > > report back?  You can get it from here:
> > >
> > > https://www.postgresql.org/message-id/CAMkU=
> 1ztk3TpQdcUNbxq93pc80FrXUjpDWL
> > > gmevbdx71ghn...@mail.gmail.com
>
> > I would be thrilled to review it, but I need a little direction as I have
> > not done a patch review before.  I have been reading through some of the
> > developer FAQ and patch info. The attached file is simply a git diff, so
> > I'm not sure how I am to use this?  Is there a specific source version I
> > can download and compile?  I know where to get the current master, etc.,
> > from git, but where can I get the patched version or what is the proper
> way
> > to apply the patch to current master?
>
> I don't think a patch review as such is necessary -- that code is very
> complex and you'd need to learn about a lot of internals (though I won't
> stop if you want to learn).  I was thinking about testing it, instead.
>
> To create a patched build,
> 1. get a clone with the branch you're on.  Assuming you're on 9.6, it'd
>be like this
>git clone  -b REL9_6_STABLE
>
> 2. apply the patch on top
>cd postgresql
>patch -p1 < /path/to/file.diff
># you could use "git apply" instead (or "git am", but not with this one)
>
> 3. configure and make
>./configure 
>make
>make install
>
> 4. run it
>initdb -D# to create a fresh datadir
>pg_ctl 
>
> You may need additional packages (zlib devel, readline devel, others;
> see https://www.postgresql.org/docs/9.6/static/installation.html)
>
> For the options in step 3 you could use whatever your current server
> has; use "pg_config --configure" to find these out.  You're gonna need
> same flags if you want to use your existing data directory.
>
> --
> Álvaro Herrerahttps://www.2ndQuadrant.com/
> PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
>

Many thanks for the great and simple explanation.

I was able to get this compiled, and ran the test before on stock 9.6.6,
then on this patched version.  I indeed reproduced it on 9.6.6, but on the
patched version, it indeed fixes my issue!

I am indeed very interested in learning more about the whole patch review
process, but I will have to save that for another day!

Let me know if you need me to check anything else!

Thanks,
Jeremy

Re: [table partitioning] How many partitions are possibel?

[Michelle Konzack]

Hi,

Am 2017-12-26 hackte Michael Paquier in die Tasten:

> On Tue, Dec 26, 2017 at 11:04:55AM +0100, Michelle Konzack wrote:
>> I like to make the partitions smaller, but the documentation say, you
>> should not dare to make several 1000 partitions..
>
> Even more than a hundred may be already risky in my opinion here. The
> issue with a large number of partitioning using the pre-9.6 grammar
> comes from the planning time which sky-rockets because the optimizer
> uses a O(N^2) algorithm to consider all the partitions. The v10 grammar
> does not take care of this planning problem as far as I recall, but v11
> gets that better for partition-wise joins if my memory does not fail me.
> --
> Michael

I think on migrating to v10, but I have to backport the PostgreSQL.

Currently I am thinking about hot to backup 28 TByte of data...
Have to dump and compress table by table and thios take endless time,
especially, if I am in Estonia and the database is in Germany.

Is there already a rease date for v11?

Thanks in avance

-- 
Michelle Konzack
00372-54541400