Pg_restore

[Nicola Contu]

Hello,
we are trying to move our postgres 10.5 instance to 11.2

We are trying to restore a DB in a pre-production env but we get an error
on the pg_restore command

[root@STAGING]#  /usr/local/pgsql11.2/bin/pg_restore -v -U postgres -L
/data/cofinder_restore.lst  -d cofinder  -j 16
/home/ncontu/data/dbbackups/C
pg_restore: connecting to database for restore
pg_restore: processing item 3098 ENCODING ENCODING
pg_restore: processing item 3099 STDSTRINGS STDSTRINGS
pg_restore: processing item 3100 SEARCHPATH SEARCHPATH
pg_restore: processing item 3101 DATABASE cofinder
pg_restore: processing item 3 SCHEMA public
pg_restore: creating SCHEMA "public"
pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 3; 2615 2200 SCHEMA public
postgres
pg_restore: [archiver (db)] could not execute query: ERROR:  schema
"public" already exists
Command was: CREATE SCHEMA public;

The dump is taken with pgsql10 binaries.

Can you help on this please?
We also have another DB, but with the other one no issues doing the same
thing.

Thanks

Re: Pg_restore

[Ron]

On 3/1/19 2:17 AM, Nicola Contu wrote:

Hello,
we are trying to move our postgres 10.5 instance to 11.2

We are trying to restore a DB in a pre-production env but we get an error 
on the pg_restore command


[root@STAGING]#  /usr/local/pgsql11.2/bin/pg_restore -v -U postgres -L 
/data/cofinder_restore.lst  -d cofinder  -j 16  /home/ncontu/data/dbbackups/C

pg_restore: connecting to database for restore
pg_restore: processing item 3098 ENCODING ENCODING
pg_restore: processing item 3099 STDSTRINGS STDSTRINGS
pg_restore: processing item 3100 SEARCHPATH SEARCHPATH
pg_restore: processing item 3101 DATABASE cofinder
pg_restore: processing item 3 SCHEMA public
pg_restore: creating SCHEMA "public"
pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 3; 2615 2200 SCHEMA 
public postgres
pg_restore: [archiver (db)] could not execute query: ERROR:  schema 
"public" already exists

    Command was: CREATE SCHEMA public;

The dump is taken with pgsql10 binaries.

Can you help on this please?
We also have another DB, but with the other one no issues doing the same 
thing.


Out of curiosity, why are you using the "-L" option instead of restoring the 
whole database?


--
Angular momentum makes the world go 'round.

Re: Pg_restore

[Nicola Contu]

I guess it depends on this
https://www.postgresql.org/message-id/15466-0b90383ff69c6e4b%40postgresql.org

Thanks

Il giorno ven 1 mar 2019 alle ore 09:17 Nicola Contu 
ha scritto:

> Hello,
> we are trying to move our postgres 10.5 instance to 11.2
>
> We are trying to restore a DB in a pre-production env but we get an error
> on the pg_restore command
>
> [root@STAGING]#  /usr/local/pgsql11.2/bin/pg_restore -v -U postgres -L
> /data/cofinder_restore.lst  -d cofinder  -j 16
> /home/ncontu/data/dbbackups/C
> pg_restore: connecting to database for restore
> pg_restore: processing item 3098 ENCODING ENCODING
> pg_restore: processing item 3099 STDSTRINGS STDSTRINGS
> pg_restore: processing item 3100 SEARCHPATH SEARCHPATH
> pg_restore: processing item 3101 DATABASE cofinder
> pg_restore: processing item 3 SCHEMA public
> pg_restore: creating SCHEMA "public"
> pg_restore: [archiver (db)] Error while PROCESSING TOC:
> pg_restore: [archiver (db)] Error from TOC entry 3; 2615 2200 SCHEMA
> public postgres
> pg_restore: [archiver (db)] could not execute query: ERROR:  schema
> "public" already exists
> Command was: CREATE SCHEMA public;
>
> The dump is taken with pgsql10 binaries.
>
> Can you help on this please?
> We also have another DB, but with the other one no issues doing the same
> thing.
>
> Thanks
>
>
>

Re: Pgbackrest Comparability issue

[David Steele]

On 3/1/19 1:37 AM, Adrian Klaver wrote:

On 2/28/19 12:28 PM, chiru r wrote:


The below steps we followed.
yum -y install perl-parent
yum -y install perl-Time-HiRes
yum -y install perl-JSON
yum -y install perl-Digest-SHA
yum -y install perl-Digest
yum -y install perl-DBD-Pg

Downloaded pgbackrest-release-1.24.zip from github and unpacked.
https://github.com/pgbackrest/pgbackrest

cp -r pgbackrest-release-1.24/lib/pgBackRest  /usr/share/perl5/
cp -r pgbackrest-release-1.24/bin/pgbackrest  /usr/bin/pgbackrest


You appear to have an old version at /bin/pgbackrest.

pgBackRest is available as a package from yum.postgresql.org.  If you 
installed Postgres from there then you just need to:


yum install pgbackrest

If this is not a fresh install and you have old copies of pgbackrest 
lying around it would be best to follow the uninstall directions here:


https://pgbackrest.org/prior/1.29/user-guide.html#installation

You may have files in other locations as well since I don't believe 
/bin/pgbackrest has even been in our install directions.


Regards,
--
-David
da...@pgmasters.net

Re: Overloaded && operator from intarray module prevents index usage.

[Andrew Gierth]

> "Ron" == Ron   writes:

 Ron> Arrays are -- by definition -- not atomic, and so they
 Ron> fundamentally break the model that relational databases are
 Ron> founded upon.  If you want to be a good database designer, don't
 Ron> use arrays.

"In theory there is no difference between theory and practice, but in
practice there is."

Sometimes a good database designer has to tell the theoreticians where
to get off, and do something more pragmatic.

-- 
Andrew (irc:RhodiumToad)

Re: Barman disaster recovery solution

[David Steele]

Achilleas,

On 2/27/19 11:39 AM, Achilleas Mantzios wrote:

On 21/2/19 9:28 π.μ., Achilleas Mantzios wrote:

On 21/2/19 9:17 π.μ., Julie Nishimura wrote:

Does anyone use this solution? any recommenations?

Thanks!


Barman will fit most requirements. PgBackRest excels when WAL traffic 
goes on 10 files/day or more. I have written an article, not yet 
publised, on a comparison on the 3 most known solutions. Will post a 
link as soon as it gets published.


Hello, as promised here is my blog :
https://severalnines.com/blog/current-state-open-source-backup-management-postgresql


Indeed, the pgBackRest user guide is a bit out of date.  I've been 
meaning to update to a newer version of Postgres but haven't had the 
chance.  This gave me the extra nudge I needed.


The docs have been update to PG10 for the next release, though the only 
visible change is to remove the `stop-auto` option since it is not 
relevant to PG10.


https://github.com/pgbackrest/pgbackrest/commit/6ce3310f8a2900d1af717da8d4c3345a9016933b

Thanks!
--
-David
da...@pgmasters.net

Thank you. Was: Where **not** to use PostgreSQL?

[Thomas Güttler Lists]

Thank you very much for your friendly answers.


I added some parts to my guidlines:

 
https://github.com/guettli/programming-guidelines/blob/master/README.rst#where-to-not-use-postgresql


Feedback is welcome.


Regards,

  Thomas Güttler


Am 28.02.19 um 12:47 schrieb Thomas Güttler:

Hi experts,

where would you suggest someone to **not** use PostgreSQL?

Why would you do this?

What alternative would you suggest instead?


Regards,
  Thomas Güttler



--
Thomas Guettler http://www.thomas-guettler.de/
I am looking for feedback: https://github.com/guettli/programming-guidelines

Re: Where **not** to use PostgreSQL?

[Merlin Moncure]

On Thu, Feb 28, 2019 at 6:24 AM Chris Travers  wrote:
>
> On Thu, Feb 28, 2019 at 1:09 PM Pavel Stehule  wrote:
>>
>> Hi
>>
>> čt 28. 2. 2019 v 12:47 odesílatel Thomas Güttler 
>>  napsal:
>>>
>>> Hi experts,
>>>
>>> where would you suggest someone to **not** use PostgreSQL?
>
>
> Hard question.  There are a lot of general places where PostgreSQL is not by 
> itself the best fit, and where a careful weighing of pros and cons would need 
> to be made before deciding to use it.
>
> Having used PostgreSQL in place of ElasticSearch for PB-scale deployments, as 
> a high-throughput queue system, and near-real-time OLAP workloads, I am 
> fairly aware of how hard it can be pushed.
>
> So the answers here are not "don't use PostgreSQL here" but "think about it 
> first and consider alternatives."
>
>
>>>
>>>
>>> Why would you do this?
>
>
> Replacing with "What would you consider to be the tradeoffs?"
>>>
>>>
>>> What alternative would you suggest instead?
>
>
> So a few possibilities:
>
> 1.  a) TB-scale full text search systems.
>  b) PostgreSQL's full text search is quite capable but not so powerful 
> that it can completely replace Lucene-based systems.  So you have to consider 
> complexity vs functionality if you are tying with other data that is already 
> in PostgreSQL.  Note further that my experience with at least ElasticSearch 
> is that it is easier to scale something built on multiple PostgreSQL 
> instances into the PB range than it is to scale ElasticSearch into the PB 
> range.
>  c) Solr or ElasticSearch

In my company we had to swap out solr for postgres.  The main
challenge was that solr's limited query language was not able to deal
with complex authorization use cases that were coming in after the
original project was deployed.  Our only solution was to  heavily
denormalize the documents so that when simple changes happened on the
OLTP side we had to push large amounts of data into SOLR.  In addition
to being slow, solr (or I guess the underlying lucene) started to
develop weird failure modes; there were unpredictable swings in memory
and/or disk usage, underlying system resource exhaustion (especially
fd).  The whole thing felt unstable; we had tested heavily with the
original requirements but the tech did not evolve with the project.

The solution was to junk the whole thing and replace it with an API
compatible version of solr in the database.  To index the document we
use a special search string with upper case keys and lower case values
in a tab delimited text string; pg_trgm/gin does the rest of the
lifting.   It can't compete with solr on best case behavior but give
much better worst case behavior, and, since we don't have to
denormalize, the system fits within memory making scaling a snap.

The moral of the story here is 'Just use postgres'.  This is not
zealotry; if I were a microsoft inclined person, I might be advising
use of sql server.  If you are not totally and completely aware of the
limits of the system you are probably operating within them.  The
database is evolving rapidly and insanely powerful servers,
supercomputers even, from the perspective of even 10 years ago, can be
had for a mouse click on the cheap.

There of course a few use cases were postgres is not optimal tech;
highly unstructured data...super high transaction rate master master
loss tolerant data archiving, warm log storage, etc.  These problems
show up quite rarely in the world of data which is generally directed
towards systems support of business applications.  If you haven't
mastered the database first, you probably shouldn't be building out
complex systems in non-database technology since you don't know what
you don't know (for example, the immense value that transactions bring
to the table).

merlin

Performance comparison between Pgsql 10.5 and Pgsql 11.2

[Nicola Contu]

Hello,
is there any reason why I am getting worse results using pgsql11.2 in
writing comparing it with pgsql 10.6?

I have two Instances, both just restored, so no bloats.
Running read queries I have pretty much same results, a little bit better
on pg11- Running writes the difference is in favour of 10.

I am expecting pg11 to be better.

Running pgbench :

PG11
[root@STAGING-CMD1 ~]#  /usr/local/pgsql11.2/bin/pgbench -t 1000 -c 20 -C
-f stress_service_order.sql cmdstaging -U admin
transaction type: stress_service_order.sql
scaling factor: 1
query mode: simple
number of clients: 20
number of threads: 1
number of transactions per client: 1000
number of transactions actually processed: 2/2
latency average = 45.322 ms
tps = 441.283336 (including connections establishing)
tps = 463.731537 (excluding connections establishing)

PG10
[root@STAGING-CMD1 ~]#  pgbench -t 1000 -c 20 -C -f
stress_service_order.sql cmdstaging -U admin
transaction type: stress_service_order.sql
scaling factor: 1
query mode: simple
number of clients: 20
number of threads: 1
number of transactions per client: 1000
number of transactions actually processed: 2/2
latency average = 44.686 ms
tps = 447.565403 (including connections establishing)
tps = 470.285561 (excluding connections establishing)

This is making a really big difference with longer queries.
Here I am updating a field in a random record.

With more transactions the difference is bigger

WITH POSTGRES 10

[root@STAGING-CMD1 ~]#  pgbench -t 10 -c 20 -C -f
stress_service_order_read.sql cmdstaging -U postgres
transaction type: stress_service_order_read.sql
scaling factor: 1
query mode: simple
number of clients: 20
number of threads: 1
number of transactions per client: 10
number of transactions actually processed: 200/200
latency average = 55.291 ms
tps = 442.1490778 (including connections establishing)
tps = 454.846844 (excluding connections establishing)

WITH POSTGRES 11
[root@STAGING-CMD1 ~]#  pgbench -t 10 -c 20 -C -f
stress_service_order_read.sql cmdstaging -U postgres
transaction type: stress_service_order_read.sql
scaling factor: 1
query mode: simple
number of clients: 20
number of threads: 1
number of transactions per client: 10
number of transactions actually processed: 200/200
latency average = 53.291 ms
tps = 375.297748 (including connections establishing)
tps = 392.316057 (excluding connections establishing)


The postgres.conf file are the same.

max_connections = 220
shared_buffers = 10GB
effective_cache_size = 120GB
work_mem = 600MB
maintenance_work_mem = 2GB
min_wal_size = 1GB
max_wal_size = 2GB
checkpoint_completion_target = 0.7
wal_buffers = 16MB
#default_statistics_target = 100

Using data_sync_retry=on doesn't make any difference.

Is there anything else changed in the default values?

Any trick?
I don't want to go live and loose performances.

Thanks a lot,
Nicola

Re: Mind of its own?

[s400t]

JD, IB, TL:

Yes!!! I found the whole bunch of tables in template 1!


It's so liberating. 

Thank you.





- Original Message -
> From: Tom Lane 
> To: s4...@yahoo.co.jp
> Cc: "pgsql-general@lists.postgresql.org" 
> Date: 2019/2/28, Thu 17:24
> Subject: Re: Mind of its own?
> 
>& lt;s4...@yahoo.co.jp> writes:
>>  I am using Postgresql 9.6, and the latest phppgadmin. OS is Debian 9.6.
> 
>>  1. Created a database.
>>  2. Created tables inside that database.
>>  3. Dropped that database.
> 
>>  Now, what happens is when I create another database with the same name or 
> different name, all those tables are already inside that database!!! I have 
> not 
> run the table create script. 
> 
> I think you created some tables in the "template1" database, which is
> (by default) what CREATE DATABASE clones to make a new DB.  Duplicating
> its contents is considered a feature not a bug.
> 
> You could manually clean out template1, or if that seems painful,
> you could drop it and recreate it by cloning template0 (see
> CREATE DATABASE's TEMPLATE option).  template0 exists precisely
> to be a virgin empty database for this sort of purpose.
> 
> (There is an interlock to prevent you from dropping either
> of these template databases accidentally.  Overriding that is
> left as an exercise for the student.)
> 
>             regards, tom lane
> 

Re: Performance comparison between Pgsql 10.5 and Pgsql 11.2

[Ray O'Donnell]

On 01/03/2019 15:01, Nicola Contu wrote:

Hello,
is there any reason why I am getting worse results using pgsql11.2 in 
writing comparing it with pgsql 10.6?


I have two Instances, both just restored, so no bloats.
Running read queries I have pretty much same results, a little bit 
better on pg11- Running writes the difference is in favour of 10.


Did you run ANALYZE on the databases after restoring?

Ray.



--
Raymond O'Donnell // Galway // Ireland
r...@rodonnell.ie

Re: PostgreSQL (linux) configuration with GSSAPI to a Windows domain

[Stephen Frost]

Greetings,

* Jean-Philippe Chenel (jp.che...@live.ca) wrote:
> I'm trying to configure authentication between PostgreSQL database server on 
> linux and Windows Active Directory.
> 
> First part of configuration is working but when I'm trying to authenticate 
> from Windows client, it is not working with message: Can't obtain database 
> list from the server. SSPI continuation error. The specified target is 
> unknown or unreachable (80090303)

> On Windows:
> 
> Domain is AD.CORP.COM
> 
> Host is: WIN.AD.CORP.COM, IP is 192.168.1.173
> 
> On Linux (Ubuntu 16.04)
> 
> hostname is UBUNTU.ad.corp.com, IP is 192.168.1.143
> 
> DNS are configured to reach the AD sytem (.173)
> 
> PostgreSQL 9.6.9 on x86_64-pc-linux-gnu (Ubuntu 9.6.9-2.pgdg16.04+1), 
> compiled by gcc (Ubuntu 5.4.0-6ubuntu1~16.04.9) 5.4.0 20160609, 64-bit

That's a rather out of date version of PG. :(  You should update to
9.6.12.

> I've created à service user called POSTGRES and a normal user in AD called 
> ubuntupg.

Did you make sure in AD to check the "User has AES256"?

> Finally I've created the SPN:
> 
> setspn -A POSTGRES/UBUNTU.ad.corp.com POSTGRES

I've not had to do this in the past..

> Generated the keytab to put on the linux server:
> 
> ktpass -out postgres.keytab -princ POSTGRES/ubuntu.ad.corp@ad.corp.com 
> -mapUser POSTGRES -pass 'thepassword' -crypto all -ptype KRB5_NT_PRINCIPAL

This looks mostly correct.

> On the linux /etc/krb5.conf:
> 
> [libdefaults]
>   debug=true
>   default_realm = AD.CORP.COM
>   dns_lookup_realm = false
>   dns_lookup_kdc = false
>   ticket_lifetime = 24h
>   renew_lifetime = 7d
>   forwardable = true
> 
> [realms]
>   AD.CORP.COM = {
> 
> kdc = WIN.AD.CORP.COM
>   }
> 
> [domain_realm]
>   ad.corp.com = AD.CORP.COM
> 
>   .ad.corp.com = AD.CORP.COM

That seems ok.

> Making this command work and klist return a ticket:
> 
> kinit -V -k -t /etc/postgresql/9.6/main/postgres.keytab 
> POSTGRES/ubuntu.ad.corp@ad.corp.com
> 
> klist -k /etc/postgresql/9.6/main/postgres.keytab
> 
> POSTGRES/ubuntu.ad.corp@ad.corp.com

You should make sure to use klist to show the KVNO and the encryption
types too (usually -e or -v works, depending on what version of Kerberos
you're using).

What does the klist on the client look like, with verbose/enctype info
shown?

> Here is the added onfiguration to postgresql.conf
> 
> krb_server_keyfile = '/etc/postgresql/9.6/main/postgres.keytab'

You might try enabling case-insensitive princs in PG using
krb_caseins_users too.

> Here is the configuration of pg_hba.conf
> 
> hostall  all0.0.0.0/0 gss
> 
> Up to here, all is working as expected, kinit with ubuntupg is also working 
> well. ubuntupg and ubunt...@ad.corp.com is also created on the database. The 
> probleme is when I try, from a Windows client, connecting to the DB.

So you're able to get in using Kerberos on the Ubuntu system?

> psql.exe -h 192.168.1.143 -U ubuntupg
> 
> Can't obtain database list from the server. SSPI continuation error. The 
> specified target is unknown or unreachable (80090303)
> 
> PostgreSQL log file show:
> 
> 2019-02-28 14:02:54.178 EST [6747] [unknown]@[unknown] LOG:  0: 
> connection received: host=192.168.1.176 port=57254
> 2019-02-28 14:02:54.178 EST [6747] [unknown]@[unknown] LOCATION:  
> BackendInitialize, postmaster.c:4188
> 2019-02-28 14:02:54.331 EST [6747] ubuntupg@ubuntupg FATAL:  28000: GSSAPI 
> authentication failed for user "ubuntupg"
> 2019-02-28 14:02:54.331 EST [6747] ubuntupg@ubuntupg DETAIL:  Connection 
> matched pg_hba.conf line 92: "hostall  all
> 0.0.0.0/0 gss"
> 2019-02-28 14:02:54.331 EST [6747] ubuntupg@ubuntupg LOCATION:  auth_failed, 
> auth.c:307
> 
> psql.exe -h 192.168.1.143 -U ubunt...@ad.corp.com
> 
> 2019-02-28 14:06:35.992 EST [6866] [unknown]@[unknown] LOG:  0: 
> connection received: host=192.168.1.176 port=57282
> 
> 2019-02-28 14:06:35.992 EST [6866] [unknown]@[unknown] LOCATION:  
> BackendInitialize, postmaster.c:4188
> 
> 2019-02-28 14:06:36.148 EST [6866] ubunt...@ad.corp.com@ubunt...@ad.corp.com 
> FATAL:  28000: GSSAPI authentication failed for user "ubunt...@ad.corp.com"
> 
> 2019-02-28 14:06:36.148 EST [6866] ubunt...@ad.corp.com@ubunt...@ad.corp.com 
> DETAIL:  Connection matched pg_hba.conf line 96: "hostall  
> all0.0.0.0/0 gss"
> 
> 2019-02-28 14:06:36.148 EST [6866] ubunt...@ad.corp.com@ubunt...@ad.corp.com 
> LOCATION:  auth_failed, auth.c:307

Have you checked to make sure that the time on the server and the time
on the client and the time on the AD server are all more-or-less in sync
(within 5 minutes)?

There is also the krbsrvname option which might be useful.

Thanks!

Stephen


signature.asc
Description: PGP signature

Re: Future Non-server Windows support???

[Bill Haught]

On 2/24/2019 12:05 PM, Bill Haught wrote:
I noticed that for 11.2, non-server versions of Windows are not listed 
as supported platforms.  Is support in the process of being dropped for 
non-server editions of Windows when 10.7 is no longer supported 
(apparently years away though)?  Or will such support be added to 11.2 
or some later versions in the future?


Thanks in advanced.




Sorry about not responding for so long. I have my own personal problems.

The main reason for any interest in PostgreSQL is that software and 
software support comes and goes.  Finally LibreOffice not only supports 
PostgreSQL directly (without having to go through ADO/JDBC/ODBC) but 
MySQL and Firebird.  It even now has a Firebird embedded database which 
also is probably overkill for LibreOffice users (or at the very least 
most of them).  SQLite is strangely absent, never mind the fact that 
they probably should have gone with it in the first place instead of HSQLDB.


I downloaded the 10.2-1 version of both EnterpriseDB and BigSQL versions 
but haven't gotten around to actually trying to get my rather small 
access format (accdb) databases in Firebird format and a backup, e.i., 
PostgeSQL format.


I am surprised that I overlooked BigSQL's explicit support for later 
versions.  Even if EnterpriseDB doesn't state that they support Windows 
10 it could be an oversight and even if not likely to run anyway.


My main concern is that Microsoft has Enterprise versions of Windows and 
versions for everything else which makes me wonder if at some point 
Windows versions for desktop use may not have features needed by some 
database applications or differences between the versions may be enough 
to necessitate slight tweaks to code and compiling additional versions.

Methods to quickly spin up copies of an existing databases

[Arjun Ranade]

I'm working on a project that requires on-demand creation of a fresh
database as quick as possible (seconds).  Essentially, this is a build
server that will require a cloned instance of Postgres to run unit tests
on.  So the pattern of use would be:


   - Build is triggered
   - New postgres instance is created and connection details are provided
   to build server
   - Build runs unit tests that connect to the database
   - Build completes, database is no longer needed and can be thrown away

The "template" database will be approximately 50gb in size so my question
is what is the fastest way to spin up new instances of this database?  I've
thought of using "CREATE DATABASE WITH TEMPLATE..." but that takes too
long.  I've also thought about cloning the $PGDATA directory, changing the
port number in postgresql.conf, and starting a new instance of postgres
pointing to the cloned data directory.

Both of these methods take longer than I'd like, so I'm wondering if anyone
else has this use case and what methods they've used to solve this.

Thanks,
Arjun

Re: Methods to quickly spin up copies of an existing databases

[Kevin Wilkinson]

if you are able/willing to use ZFS (rather than ext4, xfs, ...) to store 
your database, then it might work for you. ZFS is copy-on-write so it 
can very quickly clone a database.


kevin

On 3/1/2019 12:08 PM, Arjun Ranade wrote:
I'm working on a project that requires on-demand creation of a fresh 
database as quick as possible (seconds). Essentially, this is a build 
server that will require a cloned instance of Postgres to run unit 
tests on.  So the pattern of use would be:


  * Build is triggered
  * New postgres instance is created and connection details are
provided to build server
  * Build runs unit tests that connect to the database
  * Build completes, database is no longer needed and can be thrown away

The "template" database will be approximately 50gb in size so my 
question is what is the fastest way to spin up new instances of this 
database?  I've thought of using "CREATE DATABASE WITH TEMPLATE..." 
but that takes too long. I've also thought about cloning the $PGDATA 
directory, changing the port number in postgresql.conf, and starting a 
new instance of postgres pointing to the cloned data directory.


Both of these methods take longer than I'd like, so I'm wondering if 
anyone else has this use case and what methods they've used to solve this.


Thanks,
Arjun

Re: Methods to quickly spin up copies of an existing databases

[Kenneth Marshall]

On Fri, Mar 01, 2019 at 11:57:30AM -0800, Kevin Wilkinson wrote:
> if you are able/willing to use ZFS (rather than ext4, xfs, ...) to
> store your database, then it might work for you. ZFS is
> copy-on-write so it can very quickly clone a database.
> 
> kevin

Hi Arjun

Redhat 7 does have LVM snapshots that does something similar. Kevin is
correct, COW is the secret.

Regards,
Ken

Re: Future Non-server Windows support???

[Adrian Klaver]

On 3/1/19 11:11 AM, Bill Haught wrote:

On 2/24/2019 12:05 PM, Bill Haught wrote:
I noticed that for 11.2, non-server versions of Windows are not listed 
as supported platforms.  Is support in the process of being dropped 
for non-server editions of Windows when 10.7 is no longer supported 
(apparently years away though)?  Or will such support be added to 11.2 
or some later versions in the future?


Thanks in advanced.




Sorry about not responding for so long. I have my own personal problems.

The main reason for any interest in PostgreSQL is that software and 
software support comes and goes.  Finally LibreOffice not only supports 
PostgreSQL directly (without having to go through ADO/JDBC/ODBC) but 
MySQL and Firebird.  It even now has a Firebird embedded database which 
also is probably overkill for LibreOffice users (or at the very least 
most of them).  SQLite is strangely absent, never mind the fact that 
they probably should have gone with it in the first place instead of 
HSQLDB.


I downloaded the 10.2-1 version of both EnterpriseDB and BigSQL versions 
but haven't gotten around to actually trying to get my rather small 
access format (accdb) databases in Firebird format and a backup, e.i., 
PostgeSQL format.


I am surprised that I overlooked BigSQL's explicit support for later 
versions.  Even if EnterpriseDB doesn't state that they support Windows 
10 it could be an oversight and even if not likely to run anyway.


Ask them:

https://www.enterprisedb.com/general-inquiry-form



My main concern is that Microsoft has Enterprise versions of Windows and 
versions for everything else which makes me wonder if at some point 
Windows versions for desktop use may not have features needed by some 
database applications or differences between the versions may be enough 
to necessitate slight tweaks to code and compiling additional versions.


That happens between there desktop versions also, e.g. Home vs Premium 
vs Pro. They do not always talk to each other, especially when you cross 
numbered versions. Since Postgres is server <--> client based you might 
to look at changing where you run Postgres. Say a VM running a flavor of 
Linux.









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

RE: PostgreSQL (linux) configuration with GSSAPI to a Windows domain

[Jean-Philippe Chenel]

Dear Stephen,


Thank you very much for your help.

I think I was missing an important command in the equation.


sudo realm --verbose join ad.corp.com --user=Administrateur 
--user-principal=postgres/ubuntu.ad.corp@ad.corp.com


The Linux server as joint the AD and now, psql connection work very well from 
the clients!


Also, I've enabled the krb_caseins_users parameter and the time synchronization 
to the AD.


With best regards,


De : Stephen Frost 
Envoyé : 1 mars 2019 11:54
À : Jean-Philippe Chenel
Cc : pgsql-general@lists.postgresql.org
Objet : Re: PostgreSQL (linux) configuration with GSSAPI to a Windows domain

Greetings,

* Jean-Philippe Chenel (jp.che...@live.ca) wrote:
> I'm trying to configure authentication between PostgreSQL database server on 
> linux and Windows Active Directory.
>
> First part of configuration is working but when I'm trying to authenticate 
> from Windows client, it is not working with message: Can't obtain database 
> list from the server. SSPI continuation error. The specified target is 
> unknown or unreachable (80090303)

> On Windows:
>
> Domain is AD.CORP.COM
>
> Host is: WIN.AD.CORP.COM, IP is 192.168.1.173
>
> On Linux (Ubuntu 16.04)
>
> hostname is UBUNTU.ad.corp.com, IP is 192.168.1.143
>
> DNS are configured to reach the AD sytem (.173)
>
> PostgreSQL 9.6.9 on x86_64-pc-linux-gnu (Ubuntu 9.6.9-2.pgdg16.04+1), 
> compiled by gcc (Ubuntu 5.4.0-6ubuntu1~16.04.9) 5.4.0 20160609, 64-bit

That's a rather out of date version of PG. :(  You should update to
9.6.12.

> I've created à service user called POSTGRES and a normal user in AD called 
> ubuntupg.

Did you make sure in AD to check the "User has AES256"?

> Finally I've created the SPN:
>
> setspn -A POSTGRES/UBUNTU.ad.corp.com POSTGRES

I've not had to do this in the past..

> Generated the keytab to put on the linux server:
>
> ktpass -out postgres.keytab -princ POSTGRES/ubuntu.ad.corp@ad.corp.com 
> -mapUser POSTGRES -pass 'thepassword' -crypto all -ptype KRB5_NT_PRINCIPAL

This looks mostly correct.

> On the linux /etc/krb5.conf:
>
> [libdefaults]
>   debug=true
>   default_realm = AD.CORP.COM
>   dns_lookup_realm = false
>   dns_lookup_kdc = false
>   ticket_lifetime = 24h
>   renew_lifetime = 7d
>   forwardable = true
>
> [realms]
>   AD.CORP.COM = {
>
> kdc = WIN.AD.CORP.COM
>   }
>
> [domain_realm]
>   ad.corp.com = AD.CORP.COM
>
>   .ad.corp.com = AD.CORP.COM

That seems ok.

> Making this command work and klist return a ticket:
>
> kinit -V -k -t /etc/postgresql/9.6/main/postgres.keytab 
> POSTGRES/ubuntu.ad.corp@ad.corp.com
>
> klist -k /etc/postgresql/9.6/main/postgres.keytab
>
> POSTGRES/ubuntu.ad.corp@ad.corp.com

You should make sure to use klist to show the KVNO and the encryption
types too (usually -e or -v works, depending on what version of Kerberos
you're using).

What does the klist on the client look like, with verbose/enctype info
shown?

> Here is the added onfiguration to postgresql.conf
>
> krb_server_keyfile = '/etc/postgresql/9.6/main/postgres.keytab'

You might try enabling case-insensitive princs in PG using
krb_caseins_users too.

> Here is the configuration of pg_hba.conf
>
> hostall  all0.0.0.0/0 gss
>
> Up to here, all is working as expected, kinit with ubuntupg is also working 
> well. ubuntupg and ubunt...@ad.corp.com is also created on the database. The 
> probleme is when I try, from a Windows client, connecting to the DB.

So you're able to get in using Kerberos on the Ubuntu system?

> psql.exe -h 192.168.1.143 -U ubuntupg
>
> Can't obtain database list from the server. SSPI continuation error. The 
> specified target is unknown or unreachable (80090303)
>
> PostgreSQL log file show:
>
> 2019-02-28 14:02:54.178 EST [6747] [unknown]@[unknown] LOG:  0: 
> connection received: host=192.168.1.176 port=57254
> 2019-02-28 14:02:54.178 EST [6747] [unknown]@[unknown] LOCATION:  
> BackendInitialize, postmaster.c:4188
> 2019-02-28 14:02:54.331 EST [6747] ubuntupg@ubuntupg FATAL:  28000: GSSAPI 
> authentication failed for user "ubuntupg"
> 2019-02-28 14:02:54.331 EST [6747] ubuntupg@ubuntupg DETAIL:  Connection 
> matched pg_hba.conf line 92: "hostall  all
> 0.0.0.0/0 gss"
> 2019-02-28 14:02:54.331 EST [6747] ubuntupg@ubuntupg LOCATION:  auth_failed, 
> auth.c:307
>
> psql.exe -h 192.168.1.143 -U ubunt...@ad.corp.com
>
> 2019-02-28 14:06:35.992 EST [6866] [unknown]@[unknown] LOG:  0: 
> connection received: host=192.168.1.176 port=57282
>
> 2019-02-28 14:06:35.992 EST [6866] [unknown]@[unknown] LOCATION:  
> BackendInitialize, postmaster.c:4188
>
> 2019-02-28 14:06:36.148 EST [6866] ubunt...@ad.corp.com@ubunt...@ad.corp.com 
> FATAL:  28000: GSSAPI authentication failed for user "ubunt...@ad.corp.com"
>
> 2019-02-28 14:06:36.148 EST [6866] ubunt...@ad.corp.com@ubunt...@ad.corp.com 
> DETAIL:  Connection matched pg_hba.conf line 96: "hostall  
> all   

Re: PostgreSQL (linux) configuration with GSSAPI to a Windows domain

[Stephen Frost]

Greetings,

* Jean-Philippe Chenel (jp.che...@live.ca) wrote:
> Thank you very much for your help.
> I think I was missing an important command in the equation.
> 
>   sudo realm --verbose join ad.corp.com --user=Administrateur 
> --user-principal=postgres/ubuntu.ad.corp@ad.corp.com
> 
> The Linux server as joint the AD and now, psql connection work very well from 
> the clients!

Great!  Glad to hear you got that working.

> It makes a lot of things that I'm not accustomed too, but was very 
> interesting. I'll also look forward to the other authentication method that 
> you've talked (PAM).

I'm not sure what the discussion around PAM was getting to, though it
seemed like it was talking about using PAM at the Linux OS level to
allow access, which could be fine.

I will say that using PAM in PostgreSQL for authentication would be
worse from a security standpoint than using GSS since with PAM a
password would be sent from the client to the PG server and would be
visible to the PG server.

Thanks!

Stephen

> 
> De : Andre Piwoni 
> Envoyé : 1 mars 2019 11:44
> À : Jean-Philippe Chenel
> Cc : pgsql-general@lists.postgresql.org
> Objet : Re: PostgreSQL (linux) configuration with GSSAPI to a Windows domain
> 
> Hi Philippe,
> 
> I would increase logging level to debug since it is hard to tell from log 
> what is happening.
> Have you verified kinit for the user on the server? Sounds like you did since 
> you are running client on the server
> Also, my username in postgres database is lowercase without realm info.
> 
> At the high level, here's the setup I had:
> 
>   1.  Create AD user account for PostgreSQL UNIX service.
>   2.  Set up identity mapping for Service Principal Name (SPN) to postgres 
> user account.
> Note: Multiple service instances cannot map to the same user account, so user 
> account may be created as postgres_shortHostName
>   3.  Generate keytab for postgres service principal.
>   4.  Ensure Kerberos configuration file has been created on PostgreSQL 
> server after joining server to AD domain using SSSD and realmd utility.
>   5.  Configure PostgreSQL to use generated keytab file.
>   6.  Configure PostgreSQL host-base authentication to use GSSAPI.
> 
> My setup for PAM is using SSSD PAM module and is configured for AD:
> 
> cat /etc/pam.d/postgresql
> #%PAM-1.0
> authrequired
> pam_sss.so
> account required
> pam_sss.so
> 
> By joining domain using realm sssd you should have krb5.conf and sssd.conf 
> generated for you automatically. You should remove existing krb5.conf before 
> joining domain.
> 
> 
> cat /etc/sssd/sssd.conf
> [sssd]
> domains = 
> ad.corp.com
> config_file_version = 2
> services = nss, pam
> 
> [domain/ 
> ad.corp.com]
> ad_domain = 
> ad.corp.com
> krb5_realm = AD.CORP.COM
> realmd_tags = manages-system joined-with-samba
> cache_credentials = True
> id_provider = ad
> krb5_store_password_if_offline = True
> default_shell = /bin/bash
> ldap_id_mapping = True
> use_fully_qualified_names = False
> fallback_homedir = /home/%u
> access_provider = ad
> 
> On Fri, Mar 1, 2019 at 7:59 AM Jean-Philippe Chenel 
> mailto:jp.che...@live.ca>> wrote:
> Hi Andre,
> Thank for the followup. Here are the tests and results:
> 
> I've deleted and created service user postgres in lower case on the AD, and 
> I've made this command.
> ktpass -out postgres.keytab -princ 
> postgres/ubuntu.ad.corp@ad.corp.com
>  -mapUser AD\postgres -pass 'postgres' -mapOp add -crypto ALL -ptype 
> KRB5_NT_PRINCIPAL
> 
> Changed pg_hba.conf to
> host all all 0.0.0.0/0 gss gss include_realm=0 
> [http://krb_realm%3Dad.corp.com/]krb_realm=AD.CORP.COM
> 
> kinit is working
> kinit ubuntupg(at)AD(dot)CORP(dot)COM
> 
> Ticket cache: FILE:/tmp/krb5cc_0
> Default principal: ubunt...@ad.corp.com
> 
> Valid starting   Expires  Service principal
> 2019-03-01 10:21:50  2019-03-01 20:21:50  
> krbtgt/ad.corp@ad.corp.com
> renew until 2019-03-08 10:21:43
> 
> Here are the bad:
> root@UBUNTU:~# psql -h 192.168.20.143 -U ubuntupg
> psql: erreur de suite GSSAPI: Unspecified GSS failure.  Minor code may 
> provide more information
> erreur de suite GSSAPI: No Kerberos credentials available
> 
> Postgresql log
> 2019-03-01 09:59:13.890 EST [8913] postgres@postgres LOG:  0: c

Re: Methods to quickly spin up copies of an existing databases

[Jerry Sievers]

Kenneth Marshall  writes:

> On Fri, Mar 01, 2019 at 11:57:30AM -0800, Kevin Wilkinson wrote:
>
>> if you are able/willing to use ZFS (rather than ext4, xfs, ...) to
>> store your database, then it might work for you. ZFS is
>> copy-on-write so it can very quickly clone a database.
>> 
>> kevin
>
> Hi Arjun
>
> Redhat 7 does have LVM snapshots that does something similar. Kevin is
> correct, COW is the secret.

Going a bit further...

Any sort of storage backend that can support *atomic* snapshots across
*all* volumes (in case multiple tablespaces ar involved), can be used to
permit $instantaneous cloning where instantaneous relates to the actual
snapshot time and crash recovery.

Inability to make *atomic* snaps but perhaps seperate snaps very
quickly, combined with PITR can result in clones of high-churn systems
sized in TBs (as in our use case) to be provisioned in about 1 minute.

Nothing but the most trivial system can be cloned rapidly and perhaps
any number of times in succession without employment of
thin-provisioning, copy-on-write (as mentioned already), etc.

   Virtual copy is more and more compelling as physical
   size, or more precisely, *physical* copy time grow.

HTH



>
> Regards,
> Ken
>
>

-- 
Jerry Sievers
Postgres DBA/Development Consulting
e: postgres.consult...@comcast.net

Re: Methods to quickly spin up copies of an existing databases

[Bruce Klein]

Apologies for the low tech suggestion, but if this really is a clone of a
previously existing template, could the clone operation just be done ahead
of time? I.e., have the build server keep X copies ready for use and
generate additional copies as those are consumed, so that the cloning is no
longer on the critical path?

On Fri, Mar 1, 2019 at 11:09 AM Jerry Sievers 
wrote:

> Kenneth Marshall  writes:
>
> > On Fri, Mar 01, 2019 at 11:57:30AM -0800, Kevin Wilkinson wrote:
> >
> >> if you are able/willing to use ZFS (rather than ext4, xfs, ...) to
> >> store your database, then it might work for you. ZFS is
> >> copy-on-write so it can very quickly clone a database.
> >>
> >> kevin
> >
> > Hi Arjun
> >
> > Redhat 7 does have LVM snapshots that does something similar. Kevin is
> > correct, COW is the secret.
>
> Going a bit further...
>
> Any sort of storage backend that can support *atomic* snapshots across
> *all* volumes (in case multiple tablespaces ar involved), can be used to
> permit $instantaneous cloning where instantaneous relates to the actual
> snapshot time and crash recovery.
>
> Inability to make *atomic* snaps but perhaps seperate snaps very
> quickly, combined with PITR can result in clones of high-churn systems
> sized in TBs (as in our use case) to be provisioned in about 1 minute.
>
> Nothing but the most trivial system can be cloned rapidly and perhaps
> any number of times in succession without employment of
> thin-provisioning, copy-on-write (as mentioned already), etc.
>
>Virtual copy is more and more compelling as physical
>size, or more precisely, *physical* copy time grow.
>
> HTH
>
>
>
> >
> > Regards,
> > Ken
> >
> >
>
> --
> Jerry Sievers
> Postgres DBA/Development Consulting
> e: postgres.consult...@comcast.net
>
>

Re: Methods to quickly spin up copies of an existing databases

[Arjun Ranade]

Pre-copying is not really an option since we could potentially need 1-X
instances so it needs to be scalable.  XFS also allows for "cp --reflink"
which I could do on a PGDATA directory and then change the port number.
That's probably the method I'll try first.

We do use barman, but again a barman recover operation is pretty much just
an rsync.

I will first try XFS, then ZFS, and if those don't work, I'll look into a
SAN option.



On Fri, Mar 1, 2019 at 4:29 PM Bruce Klein  wrote:

> Apologies for the low tech suggestion, but if this really is a clone of a
> previously existing template, could the clone operation just be done ahead
> of time? I.e., have the build server keep X copies ready for use and
> generate additional copies as those are consumed, so that the cloning is no
> longer on the critical path?
>
> On Fri, Mar 1, 2019 at 11:09 AM Jerry Sievers 
> wrote:
>
>> Kenneth Marshall  writes:
>>
>> > On Fri, Mar 01, 2019 at 11:57:30AM -0800, Kevin Wilkinson wrote:
>> >
>> >> if you are able/willing to use ZFS (rather than ext4, xfs, ...) to
>> >> store your database, then it might work for you. ZFS is
>> >> copy-on-write so it can very quickly clone a database.
>> >>
>> >> kevin
>> >
>> > Hi Arjun
>> >
>> > Redhat 7 does have LVM snapshots that does something similar. Kevin is
>> > correct, COW is the secret.
>>
>> Going a bit further...
>>
>> Any sort of storage backend that can support *atomic* snapshots across
>> *all* volumes (in case multiple tablespaces ar involved), can be used to
>> permit $instantaneous cloning where instantaneous relates to the actual
>> snapshot time and crash recovery.
>>
>> Inability to make *atomic* snaps but perhaps seperate snaps very
>> quickly, combined with PITR can result in clones of high-churn systems
>> sized in TBs (as in our use case) to be provisioned in about 1 minute.
>>
>> Nothing but the most trivial system can be cloned rapidly and perhaps
>> any number of times in succession without employment of
>> thin-provisioning, copy-on-write (as mentioned already), etc.
>>
>>Virtual copy is more and more compelling as physical
>>size, or more precisely, *physical* copy time grow.
>>
>> HTH
>>
>>
>>
>> >
>> > Regards,
>> > Ken
>> >
>> >
>>
>> --
>> Jerry Sievers
>> Postgres DBA/Development Consulting
>> e: postgres.consult...@comcast.net
>>
>>

how best to specify table constraints with "create table as "

[Kevin Wilkinson]

i want to recluster an immutable table without locking the table and 
then add the table as a partition of a parent table. my plan was:


   create table tbl_cpy as select * from tbl order by c1 asc;
   alter table tbl_cpy add constraint c1 not null, c1>=lo c1i want this to run quickly. so, i populate the table with a parallel 
query (using create table as ...). i do NOT want attach partition to 
rescan the table (which it does to validate the value range) so i have 
to explicitly add constraints to tbl_cpy. but adding these constraints 
itself causes a scan of the table.


so, i want to specify the constraints when the table is created. but 
that seems not possible. if i first create an empty table with 
constraints, then i cannot populate it with a parallel query (since 
insert into is not parallel).


am i missing something? any ideas?

thanks,

kevin