[GENERAL] PostgreSQL Portable for Linux

[Alejandro Carrillo]

Good morning / afternoon / night,
I already updated the PostgreSQL Portable for Linux Debian and him flawors. 
Tested on Debian Jessie and Ubuntu 17.4. Its a 7z 
file.https://sourceforge.net/projects/pgsqlportable/files/9.6/pgsql%209.6x64.7z/download

Please look the description of the project to run it. Its start executing  "sh 
start.sh" on terminal, stop postgresql executing "sh stop.sh", restart 
postgresql executing "sh restart.sh"
Thanks to williamp with the help!

Re: [GENERAL] Partitioning and Table Inheritance

[Andrew Staller]

Hi Paul,

How much of your data is time-series in nature? Put another way, is there a
timestamp coupled with the inserted data?

Andrew

On Fri, May 12, 2017 at 4:38 PM, Ivan E. Panchenko <
i.panche...@postgrespro.ru> wrote:

> Hi
>
>
> 12.05.2017 23:22, Justin Pryzby пишет:
>
>> On Mon, May 08, 2017 at 10:12:18AM -0700, Paul A Jungwirth wrote:
>>
>>> I'm working on a problem where partitioning seems to be the right
>>> approach, but we would need a lot of partitions (say 10k or 100k).
>>> Everywhere I read that after ~100 child tables you experience
>>> problems. I have a few questions about that:
>>>
>> We use partitioning, previously one child per month (with history of 1-6
>> years); I tried using one child per day, and caused issues.
>>
>> For us, planning time is pretty unimportant (~1sec would be acceptable
>> 99% of
>> the time) but I recall seeing even more than that.  I changed to using
>> daily
>> granularity for only our largest tables, which seems to be working fine
>> for the
>> last ~9months.  So the issue isn't just "number of children" but "total
>> number
>> of tables".  I believe the problem may have been due to large
>> pg_statistic/pg_attribute and similar tables taking more than a few
>> 100MBs, and
>> potentially no longer fitting in buffer cache.
>>
>> 3. Is it true that query planning time should scale linearly as I add
>>> more child tables?
>>>
>> I believe it's understood to be super-linear:
>> https://www.postgresql.org/message-id/26761.1483989025%40sss.pgh.pa.us
>> https://www.postgresql.org/message-id/4188.1298960419%40sss.pgh.pa.us
>>
> There is an extension called pg_pathman which seriously optimizes the
> table partitioning, it might help in your case:
>
> https://github.com/postgrespro/pg_pathman
>
> See also:
>
> https://www.postgresql.org/message-id/4188.1298960419%40sss.pgh.pa.us and
>
> https://postgrespro.com/blog/pgsql/pg_pathman_e
>
>>
>> Justin
>>
>>
>> Ivan
>
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>



-- 
TimescaleDB* | *Growth & Developer Evangelism
c: 908.581.9509

335 Madison Ave.
New York, NY 10017
http://www.timescale.com/
https://github.com/timescale/timescaledb

Re: [GENERAL] Partitioning and Table Inheritance

[Ivan E. Panchenko]

Hi


12.05.2017 23:22, Justin Pryzby пишет:

On Mon, May 08, 2017 at 10:12:18AM -0700, Paul A Jungwirth wrote:

I'm working on a problem where partitioning seems to be the right
approach, but we would need a lot of partitions (say 10k or 100k).
Everywhere I read that after ~100 child tables you experience
problems. I have a few questions about that:

We use partitioning, previously one child per month (with history of 1-6
years); I tried using one child per day, and caused issues.

For us, planning time is pretty unimportant (~1sec would be acceptable 99% of
the time) but I recall seeing even more than that.  I changed to using daily
granularity for only our largest tables, which seems to be working fine for the
last ~9months.  So the issue isn't just "number of children" but "total number
of tables".  I believe the problem may have been due to large
pg_statistic/pg_attribute and similar tables taking more than a few 100MBs, and
potentially no longer fitting in buffer cache.


3. Is it true that query planning time should scale linearly as I add
more child tables?

I believe it's understood to be super-linear:
https://www.postgresql.org/message-id/26761.1483989025%40sss.pgh.pa.us
https://www.postgresql.org/message-id/4188.1298960419%40sss.pgh.pa.us
There is an extension called pg_pathman which seriously optimizes the 
table partitioning, it might help in your case:


https://github.com/postgrespro/pg_pathman

See also:

https://www.postgresql.org/message-id/4188.1298960419%40sss.pgh.pa.us and

https://postgrespro.com/blog/pgsql/pg_pathman_e


Justin



Ivan


--
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] Logical decoding CPU-bound w/ large number of tables

[Tom Lane]

Mathieu Fenniak  writes:
> I've taken your patches, Tom, and applied them to a test deployment of my
> actual application.

> The most accessible way I have to reproduce this issue is to run a
> maintenance task that we typically run during a software deployment, which
> will remove some tenant schemas from our database system.  I ran that task
> with and without the patches, and monitored how long logical replication
> stalled.  This test was on a database with 22 schemas containing 400 tables
> each (~8800 tables).

> As a baseline, PostgreSQL 9.5.6 stalled completely for 21m 13s

> PostgreSQL 9.5.6 + all three patches stalled completely for 4m 11s.

> Definitely a fantastic improvement. :-)

Thanks for confirming that these patches help in a real-world scenario.
Obviously there's still room for improvement, but I think it's worth
pushing what I've done, so I'll go do that.  I hope Andres will follow
up with trying to improve matters from the other angle.

regards, tom lane


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

Re: [GENERAL] Partitioning and Table Inheritance

[Justin Pryzby]

On Mon, May 08, 2017 at 10:12:18AM -0700, Paul A Jungwirth wrote:
> I'm working on a problem where partitioning seems to be the right
> approach, but we would need a lot of partitions (say 10k or 100k).
> Everywhere I read that after ~100 child tables you experience
> problems. I have a few questions about that:

We use partitioning, previously one child per month (with history of 1-6
years); I tried using one child per day, and caused issues.

For us, planning time is pretty unimportant (~1sec would be acceptable 99% of
the time) but I recall seeing even more than that.  I changed to using daily
granularity for only our largest tables, which seems to be working fine for the
last ~9months.  So the issue isn't just "number of children" but "total number
of tables".  I believe the problem may have been due to large
pg_statistic/pg_attribute and similar tables taking more than a few 100MBs, and
potentially no longer fitting in buffer cache.

> 3. Is it true that query planning time should scale linearly as I add
> more child tables?

I believe it's understood to be super-linear:
https://www.postgresql.org/message-id/26761.1483989025%40sss.pgh.pa.us
https://www.postgresql.org/message-id/4188.1298960419%40sss.pgh.pa.us

Justin


-- 
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] Logical decoding CPU-bound w/ large number of tables

[Mathieu Fenniak]

Hi Tom, Andres,

I've taken your patches, Tom, and applied them to a test deployment of my
actual application.

The most accessible way I have to reproduce this issue is to run a
maintenance task that we typically run during a software deployment, which
will remove some tenant schemas from our database system.  I ran that task
with and without the patches, and monitored how long logical replication
stalled.  This test was on a database with 22 schemas containing 400 tables
each (~8800 tables).

As a baseline, PostgreSQL 9.5.6 stalled completely for 21m 13s

PostgreSQL 9.5.6 + all three patches stalled completely for 4m 11s.

Definitely a fantastic improvement. :-)

This test scenario (performing the maintenance task) is unfortunately very
similar to the synthetic test... but it's the most consistent manner I've
found to reproduce this stall.  I've observed the replication stall in
production at somewhat arbitrary times, so I'm not sure if there may be
different performance profiles in more production-like usage.

Here's a CPU performance sampling from the 4m period w/ the patched
PostgreSQL.  This is from the `perf` tool on Linux, rather than the earlier
samples from my development machine on OSX.  `perf report`:
https://gist.github.com/mfenniak/9a3484c426a497f3903051d9fdf8b221  Raw perf
data (49kb bzip2):
https://www.dropbox.com/s/3jfxtg9kjzjztmp/linux-pg-walsender-patched.perf.data.bz2?dl=0

Mathieu


On Wed, May 10, 2017 at 3:10 PM, Andres Freund  wrote:

> Hi Tom, Mathieu,
>
> On 2017-05-10 17:02:11 -0400, Tom Lane wrote:
> > Mathieu Fenniak  writes:
> > > Andres, it seems like the problem is independent of having large data
> > > manipulations mixed with schema changes.  The below test case
> demonstrates
> > > it with just schema changes.
> >
> > > Tom, I've tested your patch, and it seems to have a positive impact for
> > > sure.  I've documented a test case to reproduce this issue (below), and
> > > your avoid-search-in-catcache-invalidate.patch reduces the test case
> time
> > > from 63 seconds per run to 27 seconds per run.
> >
> > Thanks for the test case.
>
> Indeed!
>
>
> > +   59.28%59.05%150349  postmaster   postgres
>   [.] hash_seq_search
> > +8.40% 8.36% 21250  postmaster   postgres
>   [.] CallSyscacheCallbacks
> > +6.37% 6.34% 16115  postmaster   postgres
>   [.] LocalExecuteInvalidationMessage
> > +5.69% 5.67% 14418  postmaster   postgres
>   [.] CatCacheInvalidate
> > +3.14% 3.12%  7942  postmaster   postgres
>   [.] SysCacheInvalidate
> > +1.72% 1.71%  4354  postmaster   postgres
>   [.] ReorderBufferCommit
> > +1.37% 1.33%  3512  postmaster   postgres
>   [.] hash_search_with_hash_value
> > +1.15% 1.15%  2913  postmaster   postgres
>   [.] InvalidateCatalogSnapshot
> >
> > I looked at the hash_seq_search time a bit more, and realized that
> > actually there's a pretty easy fix for that, which is to reduce
> > the initial size of RelfilenodeMapHash from 1024 entries to 64.
>
> Heh, that's not a bad idea. Given the table resizes automatically, there
> seems little reason not to go there.
>
> > You could quibble about where to set that exactly, but 1024 is
> > just way too many --- in your example, there are never more than
> > 5 entries in the hash, despite the presence of 1 tables in
> > the database.
>
> That's likely because there's no DML.
>
>
> > We're at a point of diminishing returns here; I think any further
> > improvement would require reducing the number of invalidation calls,
> > as Andres was speculating about upthread.  Still, this shows that
> > it doesn't take very much work to get a 10X improvement in the
> > overhead associated with inval activities.  We've never seen this
> > overhead stick out quite this much before, and maybe logical replication
> > will always be an atypical workload, but I think this may be worth
> > committing even if Andres does managed to cut the number of calls.
>
> I've seen these callsites prominently in profiles not using logical
> decoding.  Never quite as big as here, but still.  Temp table heavy
> workloads IIRC are one of the easy way to trigger it.
>
>
> > It would be interesting to see how much these patches help for your real
> > use-case, as opposed to this toy example.  Assuming that the results are
> > positive, I would advocate for back-patching these changes as far as 9.4
> > where logical decoding came in.
>
> +1.
>
>
> > BTW, I also noticed that we're getting scarily close to exceeding
> > MAX_SYSCACHE_CALLBACKS.  There are 30-some calls to
> > CacheRegisterSyscacheCallback in our code, and while I think not all of
> > them can 

Re: [GENERAL] EnterpriseDB installed PostgreSQL 9.6 vs. REPMGR. Round 2 - compilation issues.

[Adrian Klaver]

On 05/12/2017 09:19 AM, Martin Goodson wrote:

On 12/05/2017 16:59, Adrian Klaver wrote:

Configuring Automatic failover using Replication Manager 2.0 on 
PostgreSQL 9.3.5


http://raghavt.blogspot.com/2015/01/configuring-automatic-failover-using.html 



"In this post, am demonstrating Replication Manager on single 
node(localhost) on RHEL 6.5 - PostgreSQL 9.3.5. In order to achieve an 
easy and good understanding of concept I have compiled repmgr with 
EnterpriseDB One Click Installer(a pre-build binary package) instead 
of PG source."




Looks interesting! I'll give it a go tonight on a clean install and 
report back on how easy it was and how much of an idiot I am, or my 
abject failure and how much of an idiot I am :)


To make things easier I would remove the deb installed Postgres, it just 
adds complexity to the situation without providing benefit.




Many thanks!

Regards,

Martin.



--
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] EnterpriseDB installed PostgreSQL 9.6 vs. REPMGR. Round 2 - compilation issues.

[Adrian Klaver]

On 05/12/2017 09:14 AM, Martin Goodson wrote:

On 12/05/2017 16:48, Adrian Klaver wrote:

On 05/12/2017 08:30 AM, Martin Goodson wrote:

On 12/05/2017 16:18, Joshua D. Drake wrote:




The EDB installer allows you to specify where to install PostgreSQL, and 
uses /opt/PostgreSQL/ for the default. So yeah, mostly :)


My PATH is this:
/postgresql/software/pg/9.6.2/bin:/usr/local/bin:/usr/bin:/bin:/usr/local/games:/usr/games:/snap/bin 



So it should already be picking up the EDB pg_config :)


Just to be clear you changed the default install location to 
/postgresql/software/pg/9.6.2, correct?




For comparison purposes, here's pg_config when run from the cluster 
owner (testdb), which should pick up pg_config from the EnterpriseDB 
settings:


(testdb@repm:/postgresql/software/repmgr/repmgr-3.3.1$ type pg_config


Alright that is the EDB pg_config.



And THIS is the output of pg_config when run from another user:

(master@repm:~/repmgr-3.3.1$ type pg_config
pg_config is hashed (/usr/bin/pg_config)   )


The Ubuntu/Debian packages.



Should I have to somehow override any of that? Surely, if EnterpriseDB 
was built with the libs it specifies there I should be able to reference 
them?


Current output is:
testdb@repm:/postgresql/software/repmgr/repmgr-3.3.1$ sudo make 
USE_PGXS=1 install


Note the sudo, that changes you into a new environment that is not 
seeing your $PATH:


/postgresql/software/pg/9.6.2/bin:/usr/local/bin:/usr/bin:/bin:/usr/local/games:/usr/games:/snap/bin 



Hence my previous suggestion(***CORRECTED***):

PATH=:$PATH make USE_PGXS=1 clean all
sudo PATH=:$PATH make USE_PGXS=1 install

It is broken down into two steps, so the first step will clean up any 
previous attempts before going forward.





As mentioned : I don't have much experience with compiling on linux, so 
I may be missing something horribly obvious :)


Martin.



--
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] EnterpriseDB installed PostgreSQL 9.6 vs. REPMGR. Round 2 - compilation issues.

[Martin Goodson]

On 12/05/2017 16:59, Adrian Klaver wrote:

Configuring Automatic failover using Replication Manager 2.0 on 
PostgreSQL 9.3.5


http://raghavt.blogspot.com/2015/01/configuring-automatic-failover-using.html 



"In this post, am demonstrating Replication Manager on single 
node(localhost) on RHEL 6.5 - PostgreSQL 9.3.5. In order to achieve an 
easy and good understanding of concept I have compiled repmgr with 
EnterpriseDB One Click Installer(a pre-build binary package) instead 
of PG source."




Looks interesting! I'll give it a go tonight on a clean install and 
report back on how easy it was and how much of an idiot I am, or my 
abject failure and how much of an idiot I am :)


Many thanks!

Regards,

Martin.
--
Martin Goodson

"Have you thought up some clever plan, Doctor?"
"Yes, Jamie, I believe I have."
"What're you going to do?"
"Bung a rock at it."


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

Re: [GENERAL] EnterpriseDB installed PostgreSQL 9.6 vs. REPMGR. Round 2 - compilation issues.

[Martin Goodson]

On 12/05/2017 16:48, Adrian Klaver wrote:

On 05/12/2017 08:30 AM, Martin Goodson wrote:

On 12/05/2017 16:18, Joshua D. Drake wrote:




Thanks for the response. The problem is I'm kind of stuck with using 
EnterpriseDB's community edition installers. It's a company policy. I 
can't install PostgreSQL using repositories, I ** have ** to use the 
'Universal Installer' from EnterpriseDB.


Surely there must be a way to build repmgr for, and integrate it 
into, a PostgreSQL db cluster built using EnterpriseDB? Am I really 
somehow running up against a hard limit or am I simply missing 
something?


To follow up. If information in this post is still correct:

https://www.postgresql.org/message-id/CAG7mmozd7VAtdGLyE8pMaxu2KRmJxNTvLTyA0N92nEhXZ4k6hQ%40mail.gmail.com 



the EDB pg_config should be in:

/opt/PostgreSQL//bin/



The EDB installer allows you to specify where to install PostgreSQL, and 
uses /opt/PostgreSQL/ for the default. So yeah, mostly :)


My PATH is this:
/postgresql/software/pg/9.6.2/bin:/usr/local/bin:/usr/bin:/bin:/usr/local/games:/usr/games:/snap/bin

So it should already be picking up the EDB pg_config :)

For comparison purposes, here's pg_config when run from the cluster 
owner (testdb), which should pick up pg_config from the EnterpriseDB 
settings:


(testdb@repm:/postgresql/software/repmgr/repmgr-3.3.1$ type pg_config
pg_config is /postgresql/software/pg/9.6.2/bin/pg_config)

BINDIR = /postgresql/software/pg/9.6.2/bin
DOCDIR = /postgresql/software/pg/9.6.2/doc/postgresql
HTMLDIR = /postgresql/software/pg/9.6.2/doc/postgresql
INCLUDEDIR = /postgresql/software/pg/9.6.2/include
PKGINCLUDEDIR = /postgresql/software/pg/9.6.2/include/postgresql
INCLUDEDIR-SERVER = /postgresql/software/pg/9.6.2/include/postgresql/server
LIBDIR = /postgresql/software/pg/9.6.2/lib
PKGLIBDIR = /postgresql/software/pg/9.6.2/lib/postgresql
LOCALEDIR = /postgresql/software/pg/9.6.2/share/locale
MANDIR = /postgresql/software/pg/9.6.2/share/man
SHAREDIR = /postgresql/software/pg/9.6.2/share/postgresql
SYSCONFDIR = /postgresql/software/pg/9.6.2/etc/postgresql
PGXS = 
/postgresql/software/pg/9.6.2/lib/postgresql/pgxs/src/makefiles/pgxs.mk
CONFIGURE = '--enable-debug' '--with-libs=/opt/local/Current/lib' 
'--with-includes=/opt/local/Current/include/libxml2:/opt/local/Current/include' 
'--prefix=/mnt/hgfs/pginstaller.auto/server/staging/linux-x64' 
'--with-ldap' '--with-openssl' '--with-perl' '--with-python' 
'--with-tcl' 
'--with-tclconfig=/opt/local/EnterpriseDB/LanguagePack/9.6/Tcl-8.5/lib' 
'--with-pam' '--enable-thread-safety' '--with-libxml' '--with-ossp-uuid' 
'--docdir=/mnt/hgfs/pginstaller.auto/server/staging/linux-x64/doc/postgresql' 
'--with-libxslt' '--with-libedit-preferred' '--with-gssapi' 
'LD_LIBRARY_PATH=/opt/local/Current/lib' 'CFLAGS=-O2 -DMAP_HUGETLB=0x4'

CC = gcc
CPPFLAGS = -DFRONTEND -D_GNU_SOURCE -I/opt/local/Current/include/libxml2 
-I/opt/local/Current/include
CFLAGS = -Wall -Wmissing-prototypes -Wpointer-arith 
-Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute 
-Wformat-security -fno-strict-aliasing -fwrapv -g -O2 -DMAP_HUGETLB=0x4

CFLAGS_SL = -fpic
LDFLAGS = -L../../src/common -L/opt/local/Current/lib -Wl,--as-needed 
-Wl,-rpath,'/mnt/hgfs/pginstaller.auto/server/staging/linux-x64/lib',--enable-new-dtags

LDFLAGS_EX =
LDFLAGS_SL =
LIBS = -lpgcommon -lpgport -lxslt -lxml2 -lpam -lssl -lcrypto 
-lgssapi_krb5 -lz -ledit -lrt -lcrypt -ldl -lm

VERSION = PostgreSQL 9.6.2

And THIS is the output of pg_config when run from another user:

(master@repm:~/repmgr-3.3.1$ type pg_config
pg_config is hashed (/usr/bin/pg_config)   )

BINDIR = /usr/lib/postgresql/9.6/bin
DOCDIR = /usr/share/doc/postgresql-doc-9.6
HTMLDIR = /usr/share/doc/postgresql-doc-9.6
INCLUDEDIR = /usr/include/postgresql
PKGINCLUDEDIR = /usr/include/postgresql
INCLUDEDIR-SERVER = /usr/include/postgresql/9.6/server
LIBDIR = /usr/lib/x86_64-linux-gnu
PKGLIBDIR = /usr/lib/postgresql/9.6/lib
LOCALEDIR = /usr/share/locale
MANDIR = /usr/share/postgresql/9.6/man
SHAREDIR = /usr/share/postgresql/9.6
SYSCONFDIR = /etc/postgresql-common
PGXS = /usr/lib/postgresql/9.6/lib/pgxs/src/makefiles/pgxs.mk
CONFIGURE = '--with-tcl' '--with-perl' '--with-python' '--with-pam' 
'--with-openssl' '--with-libxml' '--with-libxslt' 
'--with-tclconfig=/usr/lib/x86_64-linux-gnu/tcl8.6' 
'--with-includes=/usr/include/tcl8.6' 'PYTHON=/usr/bin/python' 
'--mandir=/usr/share/postgresql/9.6/man' 
'--docdir=/usr/share/doc/postgresql-doc-9.6' 
'--sysconfdir=/etc/postgresql-common' '--datarootdir=/usr/share/' 
'--datadir=/usr/share/postgresql/9.6' 
'--bindir=/usr/lib/postgresql/9.6/bin' 
'--libdir=/usr/lib/x86_64-linux-gnu/' 
'--libexecdir=/usr/lib/postgresql/' 
'--includedir=/usr/include/postgresql/' '--enable-nls' 
'--enable-integer-datetimes' '--enable-thread-safety' 
'--enable-tap-tests' '--enable-debug' '--disable-rpath' 
'--with-uuid=e2fs' '--with-gnu-ld' '--with-pgport=5432' 
'--with-system-tzdata=/usr/share/zoneinfo' 

Re: [GENERAL] EnterpriseDB installed PostgreSQL 9.6 vs. REPMGR. Round 2 - compilation issues.

[Adrian Klaver]

On 05/12/2017 08:30 AM, Martin Goodson wrote:

On 12/05/2017 16:18, Joshua D. Drake wrote:


On 05/12/2017 08:13 AM, Martin Goodson wrote:

Hello.


Thanks for the response. The problem is I'm kind of stuck with using 
EnterpriseDB's community edition installers. It's a company policy. I 
can't install PostgreSQL using repositories, I ** have ** to use the 
'Universal Installer' from EnterpriseDB.


Surely there must be a way to build repmgr for, and integrate it into, a 
PostgreSQL db cluster built using EnterpriseDB? Am I really somehow 
running up against a hard limit or am I simply missing something?


Follow up to the follow up:

Configuring Automatic failover using Replication Manager 2.0 on 
PostgreSQL 9.3.5


http://raghavt.blogspot.com/2015/01/configuring-automatic-failover-using.html

"In this post, am demonstrating Replication Manager on single 
node(localhost) on RHEL 6.5 - PostgreSQL 9.3.5. In order to achieve an 
easy and good understanding of concept I have compiled repmgr with 
EnterpriseDB One Click Installer(a pre-build binary package) instead of 
PG source."




Regards,

Martin.



--
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] EnterpriseDB installed PostgreSQL 9.6 vs. REPMGR. Round 2 - compilation issues.

[Adrian Klaver]

On 05/12/2017 08:30 AM, Martin Goodson wrote:

On 12/05/2017 16:18, Joshua D. Drake wrote:




Thanks for the response. The problem is I'm kind of stuck with using 
EnterpriseDB's community edition installers. It's a company policy. I 
can't install PostgreSQL using repositories, I ** have ** to use the 
'Universal Installer' from EnterpriseDB.


Surely there must be a way to build repmgr for, and integrate it into, a 
PostgreSQL db cluster built using EnterpriseDB? Am I really somehow 
running up against a hard limit or am I simply missing something?


To follow up. If information in this post is still correct:

https://www.postgresql.org/message-id/CAG7mmozd7VAtdGLyE8pMaxu2KRmJxNTvLTyA0N92nEhXZ4k6hQ%40mail.gmail.com

the EDB pg_config should be in:

/opt/PostgreSQL//bin/



Regards,

Martin.



--
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] EnterpriseDB installed PostgreSQL 9.6 vs. REPMGR. Round 2 - compilation issues.

[Adrian Klaver]

On 05/12/2017 08:13 AM, Martin Goodson wrote:

Hello.

Yesterday I had problems getting repmgr installed, since our PostgreSQL 
installs to a non-standard place, and doesn't use any postgresql 
repositories/packages. The responses I got generally seemed to be 'you 
need to compile it'.


Time for round 2, trying to compile it with our own environment. Many 
apologies if I'm doing something obviously stupid, I have *very little* 
experience with compiling things on linux.


To speed things up I'm doing my attempted compiles on my laptop instead 
of our servers, since I can install packages etc as required myself 
without having to wait on our tech support team. I want to verify that I 
can get repmgr up and running on as minimal a test server as I can 
before rolling it out to slightly more hefty boxes :)


I'm running ubuntu server 17.04. The EnterpriseDB installer was used to 
quickly install a quick test PostgreSQL db, "testdb" under owning userid 
"testdb". Here is the output of pg_config:






Already installed for 9.6 - had to install it for pg_config.


That could be a problem as that is what the make is using below. That 
means it is working on the DEB installed locations not the EDB ones. You 
sure there is not a pg_config in the EDB install?



So I've already got installed the packages that PACKAGES.MD recommends, 
I guess. Attempts to compile repmgr are now failing with:


(In the directory with the expanded tar file):





testdb@repm:/postgresql/software/repmgr/repmgr-3.3.1$ sudo make 
USE_PGXS=1 install
gcc -Wall -Wmissing-prototypes -Wpointer-arith 
-Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute 
-Wformat-security -fno-strict-aliasing -fwrapv 
-fexcess-precision=standard -g -g -O2 
-fdebug-prefix-map=/build/postgresql-9.6-jQU1kx/postgresql-9.6-9.6.2=. 
-fstack-protector-strong -Wformat -Werror=format-security 
-I/usr/include/mit-krb5 -fPIC -pie -fno-omit-frame-pointer 
-I/usr/include/postgresql -I. -I./ -I/usr/include/postgresql/9.6/server 
-I/usr/include/postgresql/internal -Wdate-time -D_FORTIFY_SOURCE=2 
-D_GNU_SOURCE -I/usr/include/libxml2  -I/usr/include/tcl8.6  -c -o 
dbutils.o dbutils.c
gcc -Wall -Wmissing-prototypes -Wpointer-arith 
-Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute 
-Wformat-security -fno-strict-aliasing -fwrapv 
-fexcess-precision=standard -g -g -O2 
-fdebug-prefix-map=/build/postgresql-9.6-jQU1kx/postgresql-9.6-9.6.2=. 
-fstack-protector-strong -Wformat -Werror=format-security 
-I/usr/include/mit-krb5 -fPIC -pie -fno-omit-frame-pointer 
-I/usr/include/postgresql -I. -I./ -I/usr/include/postgresql/9.6/server 
-I/usr/include/postgresql/internal -Wdate-time -D_FORTIFY_SOURCE=2 
-D_GNU_SOURCE -I/usr/include/libxml2  -I/usr/include/tcl8.6  -c -o 
config.o config.c
gcc -Wall -Wmissing-prototypes -Wpointer-arith 
-Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute 
-Wformat-security -fno-strict-aliasing -fwrapv 
-fexcess-precision=standard -g -g -O2 
-fdebug-prefix-map=/build/postgresql-9.6-jQU1kx/postgresql-9.6-9.6.2=. 
-fstack-protector-strong -Wformat -Werror=format-security 
-I/usr/include/mit-krb5 -fPIC -pie -fno-omit-frame-pointer 
-I/usr/include/postgresql -I. -I./ -I/usr/include/postgresql/9.6/server 
-I/usr/include/postgresql/internal -Wdate-time -D_FORTIFY_SOURCE=2 
-D_GNU_SOURCE -I/usr/include/libxml2  -I/usr/include/tcl8.6  -c -o 
repmgrd.o repmgrd.c
gcc -Wall -Wmissing-prototypes -Wpointer-arith 
-Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute 
-Wformat-security -fno-strict-aliasing -fwrapv 
-fexcess-precision=standard -g -g -O2 
-fdebug-prefix-map=/build/postgresql-9.6-jQU1kx/postgresql-9.6-9.6.2=. 
-fstack-protector-strong -Wformat -Werror=format-security 
-I/usr/include/mit-krb5 -fPIC -pie -fno-omit-frame-pointer 
-I/usr/include/postgresql -I. -I./ -I/usr/include/postgresql/9.6/server 
-I/usr/include/postgresql/internal -Wdate-time -D_FORTIFY_SOURCE=2 
-D_GNU_SOURCE -I/usr/include/libxml2  -I/usr/include/tcl8.6  -c -o log.o 
log.c
gcc -Wall -Wmissing-prototypes -Wpointer-arith 
-Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute 
-Wformat-security -fno-strict-aliasing -fwrapv 
-fexcess-precision=standard -g -g -O2 
-fdebug-prefix-map=/build/postgresql-9.6-jQU1kx/postgresql-9.6-9.6.2=. 
-fstack-protector-strong -Wformat -Werror=format-security 
-I/usr/include/mit-krb5 -fPIC -pie -fno-omit-frame-pointer 
-I/usr/include/postgresql -I. -I./ -I/usr/include/postgresql/9.6/server 
-I/usr/include/postgresql/internal -Wdate-time -D_FORTIFY_SOURCE=2 
-D_GNU_SOURCE -I/usr/include/libxml2  -I/usr/include/tcl8.6  -c -o 
strutil.o strutil.c
gcc -o repmgrd -Wall -Wmissing-prototypes -Wpointer-arith 
-Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute 
-Wformat-security -fno-strict-aliasing -fwrapv 
-fexcess-precision=standard -g -g -O2 
-fdebug-prefix-map=/build/postgresql-9.6-jQU1kx/postgresql-9.6-9.6.2=. 
-fstack-protector-strong 

Re: [GENERAL] EnterpriseDB installed PostgreSQL 9.6 vs. REPMGR. Round 2 - compilation issues.

[Martin Goodson]

On 12/05/2017 16:18, Joshua D. Drake wrote:


On 05/12/2017 08:13 AM, Martin Goodson wrote:

Hello.

Yesterday I had problems getting repmgr installed, since our PostgreSQL
installs to a non-standard place, and doesn't use any postgresql
repositories/packages. The responses I got generally seemed to be 'you
need to compile it'.

I'm running ubuntu server 17.04. The EnterpriseDB installer was used to
quickly install a quick test PostgreSQL db, "testdb" under owning userid
"testdb". Here is the output of pg_config:



This is your problem. As Adrian already mentioned, you should be 
running the PGDG apt repos.


https://www.postgresql.org/download/linux/ubuntu/

Universal installers are great for a one off, "Hey let's see what we 
can do!" but they are not useful once you want to extend, have 
community support and or do things like easily install Repmgr.


Good luck!

JD

Thanks for the response. The problem is I'm kind of stuck with using 
EnterpriseDB's community edition installers. It's a company policy. I 
can't install PostgreSQL using repositories, I ** have ** to use the 
'Universal Installer' from EnterpriseDB.


Surely there must be a way to build repmgr for, and integrate it into, a 
PostgreSQL db cluster built using EnterpriseDB? Am I really somehow 
running up against a hard limit or am I simply missing something?


Regards,

Martin.
--
Martin Goodson

"Have you thought up some clever plan, Doctor?"
"Yes, Jamie, I believe I have."
"What're you going to do?"
"Bung a rock at it."



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

Re: [GENERAL] EnterpriseDB installed PostgreSQL 9.6 vs. REPMGR. Round 2 - compilation issues.

[Joshua D. Drake]

On 05/12/2017 08:13 AM, Martin Goodson wrote:

Hello.

Yesterday I had problems getting repmgr installed, since our PostgreSQL
installs to a non-standard place, and doesn't use any postgresql
repositories/packages. The responses I got generally seemed to be 'you
need to compile it'.

I'm running ubuntu server 17.04. The EnterpriseDB installer was used to
quickly install a quick test PostgreSQL db, "testdb" under owning userid
"testdb". Here is the output of pg_config:



This is your problem. As Adrian already mentioned, you should be running 
the PGDG apt repos.


https://www.postgresql.org/download/linux/ubuntu/

Universal installers are great for a one off, "Hey let's see what we can 
do!" but they are not useful once you want to extend, have community 
support and or do things like easily install Repmgr.


Good luck!

JD

--
Command Prompt, Inc.  http://the.postgres.company/
+1-503-667-4564
PostgreSQL Centered full stack support, consulting and development.
Everyone appreciates your honesty, until you are honest with them.
Unless otherwise stated, opinions are my own.


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

[GENERAL] EnterpriseDB installed PostgreSQL 9.6 vs. REPMGR. Round 2 - compilation issues.

[Martin Goodson]

Hello.

Yesterday I had problems getting repmgr installed, since our PostgreSQL 
installs to a non-standard place, and doesn't use any postgresql 
repositories/packages. The responses I got generally seemed to be 'you 
need to compile it'.


Time for round 2, trying to compile it with our own environment. Many 
apologies if I'm doing something obviously stupid, I have *very little* 
experience with compiling things on linux.


To speed things up I'm doing my attempted compiles on my laptop instead 
of our servers, since I can install packages etc as required myself 
without having to wait on our tech support team. I want to verify that I 
can get repmgr up and running on as minimal a test server as I can 
before rolling it out to slightly more hefty boxes :)


I'm running ubuntu server 17.04. The EnterpriseDB installer was used to 
quickly install a quick test PostgreSQL db, "testdb" under owning userid 
"testdb". Here is the output of pg_config:


testdb@repm:/postgresql/software/repmgr/repmgr-3.3.1$ pg_config
BINDIR = /postgresql/software/pg/9.6.2/bin
DOCDIR = /postgresql/software/pg/9.6.2/doc/postgresql
HTMLDIR = /postgresql/software/pg/9.6.2/doc/postgresql
INCLUDEDIR = /postgresql/software/pg/9.6.2/include
PKGINCLUDEDIR = /postgresql/software/pg/9.6.2/include/postgresql
INCLUDEDIR-SERVER = /postgresql/software/pg/9.6.2/include/postgresql/server
LIBDIR = /postgresql/software/pg/9.6.2/lib
PKGLIBDIR = /postgresql/software/pg/9.6.2/lib/postgresql
LOCALEDIR = /postgresql/software/pg/9.6.2/share/locale
MANDIR = /postgresql/software/pg/9.6.2/share/man
SHAREDIR = /postgresql/software/pg/9.6.2/share/postgresql
SYSCONFDIR = /postgresql/software/pg/9.6.2/etc/postgresql
PGXS = 
/postgresql/software/pg/9.6.2/lib/postgresql/pgxs/src/makefiles/pgxs.mk
CONFIGURE = '--enable-debug' '--with-libs=/opt/local/Current/lib' 
'--with-includes=/opt/local/Current/include/libxml2:/opt/local/Current/include' 
'--prefix=/mnt/hgfs/pginstaller.auto/server/staging/linux-x64' 
'--with-ldap' '--with-openssl' '--with-perl' '--with-python' 
'--with-tcl' 
'--with-tclconfig=/opt/local/EnterpriseDB/LanguagePack/9.6/Tcl-8.5/lib' 
'--with-pam' '--enable-thread-safety' '--with-libxml' '--with-ossp-uuid' 
'--docdir=/mnt/hgfs/pginstaller.auto/server/staging/linux-x64/doc/postgresql' 
'--with-libxslt' '--with-libedit-preferred' '--with-gssapi' 
'LD_LIBRARY_PATH=/opt/local/Current/lib' 'CFLAGS=-O2 -DMAP_HUGETLB=0x4'

CC = gcc
CPPFLAGS = -DFRONTEND -D_GNU_SOURCE -I/opt/local/Current/include/libxml2 
-I/opt/local/Current/include
CFLAGS = -Wall -Wmissing-prototypes -Wpointer-arith 
-Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute 
-Wformat-security -fno-strict-aliasing -fwrapv -g -O2 -DMAP_HUGETLB=0x4

CFLAGS_SL = -fpic
LDFLAGS = -L../../src/common -L/opt/local/Current/lib -Wl,--as-needed 
-Wl,-rpath,'/mnt/hgfs/pginstaller.auto/server/staging/linux-x64/lib',--enable-new-dtags

LDFLAGS_EX =
LDFLAGS_SL =
LIBS = -lpgcommon -lpgport -lxslt -lxml2 -lpam -lssl -lcrypto 
-lgssapi_krb5 -lz -ledit -lrt -lcrypt -ldl -lm

VERSION = PostgreSQL 9.6.2
testdb@repm:/postgresql/software/repmgr/repmgr-3.3.1$

PostgreSQL is up and running nicely:

testdb@repm:/postgresql/software/repmgr/repmgr-3.3.1$ pg_ctl status
pg_ctl: server is running (PID: 1433)
/postgresql/software/pg/9.6.2/bin/postgres


First attempt at compilation was unsuccessful and so I followed the 
advice in PACKAGES.md:When building repmgr against a Debian packages 
build, you may discover that some development packages are needed as 
well. You will need the following development packages installed:


|sudo apt-get install libxslt-dev libxml2-dev libpam-dev libedit-dev|

Here's the output I now get when checking those packages:

testdb@repm:/postgresql/software/repmgr/repmgr-3.3.1$ sudo apt-get 
install libxslt-dev libxml2-dev libpam-dev libedit-dev

Reading package lists... Done
Building dependency tree
Reading state information... Done
Note, selecting 'libxslt1-dev' instead of 'libxslt-dev'
Note, selecting 'libpam0g-dev' instead of 'libpam-dev'
libedit-dev is already the newest version (3.1-20160903-3).
libpam0g-dev is already the newest version (1.1.8-3.2ubuntu2).
libxml2-dev is already the newest version (2.9.4+dfsg1-2.2).
libxslt1-dev is already the newest version (1.1.29-2ubuntu0.1).
0 to upgrade, 0 to newly install, 0 to remove and 4 not to upgrade.

It also recommends installing the postgres server dev

|sudo apt-get install postgresql-server-dev-9.0|

testdb@repm:/postgresql/software/repmgr/repmgr-3.3.1$ sudo apt-get 
install postgresql-server-dev-9.6

Reading package lists... Done
Building dependency tree
Reading state information... Done
postgresql-server-dev-9.6 is already the newest version (9.6.2-1).
0 to upgrade, 0 to newly install, 0 to remove and 4 not to upgrade.

Already installed for 9.6 - had to install it for pg_config.

So I've already got installed the packages that PACKAGES.MD recommends, 
I guess. Attempts to compile repmgr are now failing with:

Re: [GENERAL] Top posting....

[Francisco Olarte]

George:

On Fri, May 12, 2017 at 2:23 AM, George Neuner  wrote:

> I agree 100%.  But excessive brevity can make it so a reader can't
> follow the conversation.  Users of web forums often assume *you* can
> easily look back up the thread because *they* can.  In my experience,
> it isn't always easy to do.

Excessive = too much, normally implies bad things.

Francisco Olarte.


-- 
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] Top posting....

[Thomas Kellerer]

Tom Lane schrieb am 11.05.2017 um 19:43:
> Bottom posting without trimming is just an awful combination:
> whatever you do, don't do that.
Amen to that. 





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