Re: 9.6.9 Default configuration for a default installation but different with-krb-srvnam

[Stephen Frost]

Greetings,

* Jean-Philippe Chenel (jp.che...@live.ca) wrote:
> You're absolutely right, the mapping work very well.

Great, glad to hear it.

> I've created 2 "service user" on Active Directory (postgres and 
> postgres_dev), and generated the keytab like this:
> 
> ktpass -out postgres_pg1.keytab -princ postgres/pgdomt1.ad@ad.com 
> -mapUser AD\postgres -pass 'UserPass1' -mapOp add -crypto ALL -ptype 
> KRB5_NT_PRINCIPAL
> 
> ktpass -out postgres_pg2.keytab -princ postgres/pgdomt2.ad@ad.com 
> -mapUser AD\postgres_dev -pass 'UserPass2' -mapOp add -crypto ALL -ptype 
> KRB5_NT_PRINCIPAL

I would strongly suggest you use passwords that are randomly generated
and not sent to a public, archived, mailing list.  If someone knows the
password, they can impersonate the server.

Thanks!

Stephen


signature.asc
Description: PGP signature

RE: 9.6.9 Default configuration for a default installation but different with-krb-srvnam

[Jean-Philippe Chenel]

Dear Stephen,

You're absolutely right, the mapping work very well.


I've created 2 "service user" on Active Directory (postgres and postgres_dev), 
and generated the keytab like this:

ktpass -out postgres_pg1.keytab -princ postgres/pgdomt1.ad@ad.com -mapUser 
AD\postgres -pass 'UserPass1' -mapOp add -crypto ALL -ptype KRB5_NT_PRINCIPAL

ktpass -out postgres_pg2.keytab -princ postgres/pgdomt2.ad@ad.com -mapUser 
AD\postgres_dev -pass 'UserPass2' -mapOp add -crypto ALL -ptype 
KRB5_NT_PRINCIPAL

Thank you very much for your help.


De : Stephen Frost 
Envoyé : 29 avril 2019 13:35
À : Jean-Philippe Chenel
Cc : pgsql-general@lists.postgresql.org
Objet : Re: 9.6.9 Default configuration for a default installation but 
different with-krb-srvnam

Greetings,

* Jean-Philippe Chenel (jp.che...@live.ca) wrote:
> If I understand, the mapping can be done in the pg_ident.conf file ?

No, you do the mapping in AD.

Look at the '/princ' and '/mapuser' options used in the ktpass command
here:

https://info.crunchydata.com/blog/windows-active-directory-postgresql-gssapi-kerberos-authentication
How to setup Windows Active Directory with PostgreSQL GSSAPI Kerberos 
Authentication - 
info.crunchydata.com
info.crunchydata.com
PostgreSQL provides a many authentications methods to allow you to pick the one 
that makes the most sense for your environment. This guide will show you how to 
use your Windows Active Directory to authenticate to PostgreSQL via GSSAPI 
Kerberos authentication.




Thanks,

Stephen

Re: How to execute .sql file inside a postgres schema

[Adrian Klaver]

On 4/29/19 7:02 AM, Daulat Ram wrote:

Hello team,

I have a database name “kbdb” that is having a schema “kb” and I want to 
execute the test.sql file inside this schema,


Please help how we can do that.


You can do as Daniel suggested but be aware that it is equivalent to:

https://www.postgresql.org/docs/11/sql-set.html
SET search_path TO value

That means if you have objects in the script that are not schema 
qualified and need to point to other schemas then things will fail. A 
longer term solution would to schema qualify the objects in your script. 
This is assuming that the script is not generic and is meant to 'float' 
from schema to schema.




Regards,

Daulat




--
Adrian Klaver
adrian.kla...@aklaver.com

Re: 9.6.9 Default configuration for a default installation but different with-krb-srvnam

[Stephen Frost]

Greetings,

* Jean-Philippe Chenel (jp.che...@live.ca) wrote:
> If I understand, the mapping can be done in the pg_ident.conf file ?

No, you do the mapping in AD.

Look at the '/princ' and '/mapuser' options used in the ktpass command
here:

https://info.crunchydata.com/blog/windows-active-directory-postgresql-gssapi-kerberos-authentication

Thanks,

Stephen


signature.asc
Description: PGP signature

RE: 9.6.9 Default configuration for a default installation but different with-krb-srvnam

[Jean-Philippe Chenel]

Dear Stephen,


If I understand, the mapping can be done in the pg_ident.conf file ?

Thank you very much for your workaround,

Jean-Philippe



De : Stephen Frost 
Envoyé : 29 avril 2019 10:22
À : Jean-Philippe Chenel
Cc : pgsql-general@lists.postgresql.org
Objet : Re: 9.6.9 Default configuration for a default installation but 
different with-krb-srvnam

Greetings,

* Jean-Philippe Chenel (jp.che...@live.ca) wrote:
> I've configured the GSSAPI authentication with MS Active Directory and it 
> works very well.

Glad to hear that.

> The problem is that we have a dev and prod environment and each server must 
> be configured with gssapi again the domain controller. The default user is 
> "postgres" and it cannot be bound to both postgresql server at the same time 
> to the same userPrincipalName on the Windows domain controller.

You should be able to just use a different user in AD for each server,
and then map 'postgres/dev.hostname@REALM' to the dev user and
'postgres/prod.hostname@REALM' to the prod user in AD and everything
should work just fine.

> 1. So my question is, how can I compile this version of postgresql (9.6.9) 
> and have the same real things of the default previously installed version, 
> but with the --with-krb-srvnam=POSTGRES_DEV to change the default user name? 
> So one server will have the postgres user and the other one will have 
> postgres_dev user.

You shouldn't need to compile with a different krb srvname (and I
wouldn't recommend that you do).  If you *really* want to have a
different srvname, you don't have to recompile anything if you update
your client and server side configs to match whatever you want the
srvname to be, but, again, you shouldn't need to do that and doing so is
just confusing (particularly building different binaries, since then
some binaries will think 'postgres' is the default srvname and some will
think 'whatever' is, while otherwise being the same...).

> 3. Maybe can we configure things differently, if something else can be done 
> to make this work, I'm open to suggestions.

Create different users in AD for each and then map to them.  You don't
need to have a different srvname.

Thanks,

Stephen

Re: How to execute .sql file inside a postgres schema

[Daniel Westermann (DWE)]

>>I have a database name “kbdb” that is having a schema “kb” and I want to 
>>execute the test.sql file inside >>this schema,



set schema 'your_schema';


Cheers,

Daniel

Upgrading locale issues

[rihad]

Hi. Today we run pg_ctl promote on a slave server (10.7) and started 
using it as a master. The OS also got upgraded FreeBSD 10.4 -> FreeBSD 
11.2. And you guessed it, most varchar indexes got corrupted because 
system local changed in subtle ways. So I created the extension amcheck 
and reindexed all bad indexes one by one. Is there any way to prevent 
such things in the future? Will switching to ICU fix all such issues? 
The problem with it is that ICU collations are absent in pg_collation, 
initdb should be run to create them, but pg_basebackup only runs on an 
empty base directory, so I couldn't run initdb + pg_basebackup to 
prepare the replica server. I believe I can run the create collation 
command manually, but what would it look like for en-x-icu?



CREATE COLLATION "en-ix-icu" (provider = icu, locale = 'en-x-icu');


is that it? But what about version etc?


    collname    | collnamespace | collowner | collprovider | 
collencoding |    collcollate    | collctype | collversion


 en-x-icu   |    11 |    10 | i    
|   -1 | en    | en    | 153.88



Thanks.

How to execute .sql file inside a postgres schema

[Daulat Ram]

Hello team,

I have a database name "kbdb" that is having a schema "kb" and I want to 
execute the test.sql file inside this schema,

Please help how we can do that.

Regards,
Daulat

Re: 9.6.9 Default configuration for a default installation but different with-krb-srvnam

[Stephen Frost]

Greetings,

* Jean-Philippe Chenel (jp.che...@live.ca) wrote:
> I've configured the GSSAPI authentication with MS Active Directory and it 
> works very well.

Glad to hear that.

> The problem is that we have a dev and prod environment and each server must 
> be configured with gssapi again the domain controller. The default user is 
> "postgres" and it cannot be bound to both postgresql server at the same time 
> to the same userPrincipalName on the Windows domain controller.

You should be able to just use a different user in AD for each server,
and then map 'postgres/dev.hostname@REALM' to the dev user and
'postgres/prod.hostname@REALM' to the prod user in AD and everything
should work just fine.

> 1. So my question is, how can I compile this version of postgresql (9.6.9) 
> and have the same real things of the default previously installed version, 
> but with the --with-krb-srvnam=POSTGRES_DEV to change the default user name? 
> So one server will have the postgres user and the other one will have 
> postgres_dev user.

You shouldn't need to compile with a different krb srvname (and I
wouldn't recommend that you do).  If you *really* want to have a
different srvname, you don't have to recompile anything if you update
your client and server side configs to match whatever you want the
srvname to be, but, again, you shouldn't need to do that and doing so is
just confusing (particularly building different binaries, since then
some binaries will think 'postgres' is the default srvname and some will
think 'whatever' is, while otherwise being the same...).

> 3. Maybe can we configure things differently, if something else can be done 
> to make this work, I'm open to suggestions.

Create different users in AD for each and then map to them.  You don't
need to have a different srvname.

Thanks,

Stephen


signature.asc
Description: PGP signature

Re: Missing pg_config on SuSE SLES 12 for PostgreSQL 10

[Matthias Apitz]

El día Monday, April 29, 2019 a las 10:24:34AM +0200, Christoph Moench-Tegeder 
escribió:

> ## Matthias Apitz (g...@unixarea.de):
> 
> > The server in question is SLES12-SP3 and I can't update to SP4 at the
> > moment. I have installed the following pkg:
> 
> > # rpm -qa | egrep 'postgre|libpq' | sort
> > libpq5-10.6-1.6.1.x86_64
> 
> Ah, right, there's also a postgresql10 in SP3.
> 
> > How can I activated/installed the "SDK" repositories to get rid of the
> > PGDG packages? Thanks in advance.
> 
> Use SUSEConnect... "SUSEConnect --list-extensions" will show all available
> extensions and the command lines to activate/deactivate them (the SDK
> would be registered by running "SUSEConnect -p sle-sdk/12.3/x86_64", but
> check first...). Alternatively you could use yast and friends.
> You might have to deactivate the PGDG repos and remove those rpms, else
> zypper might try to "upgrade" the Suse packages with the PGDG ones.

Thanks. This gives:

# SUSEConnect --status
[{"identifier":"SLES","version":"12.3","arch":"x86_64","status":"Registered"}]
# SUSEConnect --list-extensions
AVAILABLE EXTENSIONS AND MODULES

MORE INFORMATION

You can find more information about available modules here:
https://www.suse.com/products/server/features/modules.html

# SUSEConnect -p sle-sdk/12.3/x86_64
Error: Registration server returned 'Product not (fully) mirrored on
this server.
Mirroring of the following repos has to be enabled on the SMT server:
  * SLE-SDK12-SP3-Updates (sle-sdk,12.3,x86_64)
  * SLE-SDK12-SP3-Pool (sle-sdk,12.3,x86_64)
' (422)
# SUSEConnect --list-extensions 
 
AVAILABLE EXTENSIONS AND MODULES

MORE INFORMATION

You can find more information about available modules here:
https://www.suse.com/products/server/features/modules.html

I have to check with our IT department about this.

Regaars

matthias
-- 
Matthias Apitz, ✉ g...@unixarea.de, http://www.unixarea.de/ +49-176-38902045
Public GnuPG key: http://www.unixarea.de/key.pub

Re: Missing pg_config on SuSE SLES 12 for PostgreSQL 10

[Christoph Moench-Tegeder]

## Matthias Apitz (g...@unixarea.de):

> The server in question is SLES12-SP3 and I can't update to SP4 at the
> moment. I have installed the following pkg:

> # rpm -qa | egrep 'postgre|libpq' | sort
> libpq5-10.6-1.6.1.x86_64

Ah, right, there's also a postgresql10 in SP3.

> How can I activated/installed the "SDK" repositories to get rid of the
> PGDG packages? Thanks in advance.

Use SUSEConnect... "SUSEConnect --list-extensions" will show all available
extensions and the command lines to activate/deactivate them (the SDK
would be registered by running "SUSEConnect -p sle-sdk/12.3/x86_64", but
check first...). Alternatively you could use yast and friends.
You might have to deactivate the PGDG repos and remove those rpms, else
zypper might try to "upgrade" the Suse packages with the PGDG ones.

Else: get PostgreSQL 11 from the PGDG repos and hope Suse doesn't
give you their own postgresql11 packages sometime soon.

Regards,
Christoph

-- 
Spare Space

Re: Missing pg_config on SuSE SLES 12 for PostgreSQL 10

[Matthias Apitz]

El día sábado, abril 27, 2019 a las 04:11:06p. m. +0200, Christoph 
Moench-Tegeder escribió:

> ## Matthias Apitz (g...@unixarea.de):
> 
> > To get Perl's DBD::Pg compiled now I really do need the pg_config tool,
> > but I can't figure out how to get it. I see the following RPM (the ones
> > with an 'i' or 'i+' are installed):
> 
> Um. Which postgresql10-* packages are that?
> SLES12SP4 has "postgresql10" and friends:
> Information for package postgresql10:
> -
> Repository : SLES12-SP4-Updates
> Name   : postgresql10
> Version: 10.6-1.6.1
> Arch   : x86_64
> Vendor : SUSE LLC 
> 
> And there are the PGDG packages:
> cmt@squirrel:/tmp$ rpm -qp --info postgresql10-10.7-1PGDG.sles12.x86_64.rpm
> warning: postgresql10-10.7-1PGDG.sles12.x86_64.rpm: Header V4 DSA/SHA1 
> Signature, key ID 442df0f8: NOKEY
> Name: postgresql10
> Version : 10.7
> Release : 1PGDG.sles12
> Architecture: x86_64
> Install Date: (not installed)
> Group   : Applications/Databases
> Size: 15066385
> License : PostgreSQL
> 
> If you are on a SLES without postgresql10 packages and use the PGDG
> packages, you'll find pg_config in the "postgresql10" package (at
> least it's in that postgresql10-10.7-1PGDG.sles12.x86_64.rpm I used above,
> and the spec file shows the same...), the full path would be
>  "/usr/pgsql-10/bin/pg_config".
> 
> If you're on SLES12 with Suse's postgresql10 packages, you'll find
> pg_config in "postgresql10-devel" - which you only get if you have
> activated/installed the "SDK" repositories. The full path for pg_config
> with this package is
>   "/usr/lib/postgresql10/bin/pg_config".
> 
> If you are on a SLES with postgresql10 packages and are mixing that
> with the PGDG packages, you might be in a mess. And as far as I know
> there's no good way to make sure which packages you get, as zypper
> tells me "Repository priorities are without effect. All enabled repositories
> share the same priority.".

Hello Christoph,

The server in question is SLES12-SP3 and I can't update to SP4 at the
moment. I have installed the following pkg:

# cat /etc/os-release
NAME="SLES"
VERSION="12-SP3"
VERSION_ID="12.3"
PRETTY_NAME="SUSE Linux Enterprise Server 12 SP3"
ID="sles"
ANSI_COLOR="0;32"
CPE_NAME="cpe:/o:suse:sles:12:sp3"

# rpm -qa | egrep 'postgre|libpq' | sort
libpq5-10.6-1.6.1.x86_64
libpqos1-0.1.4-3.1.x86_64
libpqxx-5.0.1-2.sles12.x86_64
libpqxx-devel-5.0.1-2.sles12.x86_64
postgresql-init-10-17.20.1.noarch
postgresql-jdbc-9.4-1.1.noarch
postgresql10-10.6-1.6.1.x86_64
postgresql10-contrib-10.6-1.6.1.x86_64
postgresql10-devel-10.4-1PGDG.sles12.x86_64
postgresql10-docs-10.6-1.6.1.noarch
postgresql10-libs-10.4-1PGDG.sles12.x86_64
postgresql10-server-10.6-1.6.1.x86_64

How can I activated/installed the "SDK" repositories to get rid of the
PGDG packages? Thanks in advance.

Regards

matthias

-- 
Matthias Apitz, ✉ g...@unixarea.de, http://www.unixarea.de/ +49-176-38902045
Public GnuPG key: http://www.unixarea.de/key.pub


signature.asc
Description: PGP signature

Re: Thousands of partitions performance questions

[David Rowley]

On Mon, 29 Apr 2019 at 19:20, Shai Cantor  wrote:
> Some notes I haven't shared or were not clear enough on the previous post.
>
> Data is inserted using the copy command only and in an offline manner. 
> Meaning, no user action creates or updates the data. An offline job runs is. 
> Number of inserts can reach up to 1500 a day.
> Queries are only on a single partition
>
> Can that ease the performance?

Yes, COPY will be more efficient. In PG11 it still means locking all
partitions but that becomes more worthwhile the more tuples that are
inserted at once.  The same goes for INSERT with multiple rows in the
VALUES clause.

> Will querying directly the partition tables help?

Yes. If you're able to determine which partition to query from within
the application and write that in the query instead, then this will be
much less planner overhead for PG11. It'll be pretty much the same as
if you were querying a normal table.

-- 
 David Rowley   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services

Re: Thousands of partitions performance questions

[Shai Cantor]

Thanks a lot for your feedback.
We're gonna user PG 11 on AWS RDS.
I will do some workload simulations.
Some notes I haven't shared or were not clear enough on the previous post.

   1. *Data is inserted using the copy command only* and in an offline
   manner. Meaning, no user action creates or updates the data. An offline job
   runs is. Number of inserts can reach up to 1500 a day.
   2. *Queries are only on a single partition*



   - Can that ease the performance?
   - Will querying directly the partition tables help?


On Mon, Apr 29, 2019 at 8:46 AM David Rowley 
wrote:

> On Mon, 29 Apr 2019 at 17:13, Shai Cantor  wrote:
> > Will the db hold 135000 (45000 * 3 months) partitions under the
> assumption that I query only 1 partition?
> > Should I model it differently in terms of schema, partitions etc.?
>
> Which PG version?
>
> Before PG11 the query planner pruned unneeded partition by looking at
> each partition and determining if the partition constraint could not
> match the base quals on the query.  PG11 improved this by adding a
> smarter and faster algorithm to get rid of non-matching partitions,
> however, this really only speeds things up a little as it only really
> allows the planner to skip generating paths for these partitions,
> certain meta-data is still loaded, which is not really slow per
> partition, but it is slow if you have thousands of partitions.
>
> PG11 is also pretty bad at planning UPDATE/DELETEs to partitioned
> tables with a large number of partitions. You'll at best suffer from
> high planning times to plan these queries and at worst suffer out of
> memory errors with that many partitions.
>
> The yet to be released PG12 improves both of these deficiencies
> providing you can eliminate the majority of partitions during query
> planning. PG12 also improves the performance of INSERT into a
> partitioned table, wherein PG11 a lock was obtained on every
> partition, with PG12 we only grab a lock on a partition the first time
> the INSERT command inserts a row into it. If you're just INSERTing 1
> row per command into a partitioned table with many partitions then
> this makes a pretty big difference.
>
> Depending on the types of query you're running it's likely not a good
> idea to go above 100 or so partitions with PG11. You might get away
> with more if you're running a more data-warehouse type load, i.e fewer
> but longer running queries, but for a more OLTP type workload, with
> more queries and lower latencies, then you may struggle to cope with a
> dozen.
>
> I'd recommend you do workload simulations with whatever number you
> choose and ensure performance is to the level you require before
> getting in too deep with your design.
>
> If your go-live date is near the end of the year or beyond, then it
> might be a good idea to start testing with PG12 right away. The
> release date for that will likely be around the middle of October.
>
> --
>  David Rowley   http://www.2ndQuadrant.com/
>  PostgreSQL Development, 24x7 Support, Training & Services
>