Re: Barman versus pgBackRest

[Ron]

On 03/09/2018 08:56 AM, David Steele wrote:
[snip]

About pgBarman, I like :
- be able restore on a remote server from the backup server

This a good feature, and one that has been requested for pgBackRest. You
can do this fairly trivially with ssh, however, so it generally hasn't
been a big deal for people.  Is there a particular reason you need this
feature?


(Sorry to dredge up this old thread.)

Do you just change the IP address of the "restore target"?


- use replication slots for backingup wal on the backup server.

Another good feature.  We have not added it yet because pgBackRest was
originally written for very high-volume clusters (100K+ WAL per day) and
our parallel async feature answers that need much better.  We recommend
a replicated standby for more update-to-date data.


Every N minutes you copy the WAL files to the backup server?


--
Angular momentum makes the world go 'round.

Re: Barman versus pgBackRest

[Thomas Poty]

> Do you just change the IP address of the "restore target"?
Do you expect a typical restore command? If yes, here is a small bash
script I use for check restore ...

barmanBackupID=""
barmanBackupServer=$1
if [ 1 -eq $# ]; then
echo ${barmanBackupServer}
barmanBackupID=$(barman list-backup ${barmanBackupServer} |tac|tail
-n2|head -n1| awk '{print $2}')
barman recover --jobs 4 --remote-ssh-command "ssh postgres@srv397"
--target-time "$(date +%Y-%m-%d) 23:30:00.000" --target-action pause
${barmanBackupServer} ${barmanBackupID}
/mnt/data1/postgresql_postgresqlbackupintegritychecker_general/data
else
echo "server name expected as the only argument of script"
fi

> Every N minutes you copy the WAL files to the backup server?
Currently I use barman configured with replication slot (for minimum data
loss without beeing synchronous) and wal archiving to the backup server.
Wal archiving to the backup server is done through archive_command of
postgresql.conf

Is it answer you questions?

Le mar. 4 sept. 2018 à 13:42, Ron  a écrit :

> On 03/09/2018 08:56 AM, David Steele wrote:
> [snip]
> >> About pgBarman, I like :
> >> - be able restore on a remote server from the backup server
> > This a good feature, and one that has been requested for pgBackRest. You
> > can do this fairly trivially with ssh, however, so it generally hasn't
> > been a big deal for people.  Is there a particular reason you need this
> > feature?
>
> (Sorry to dredge up this old thread.)
>
> Do you just change the IP address of the "restore target"?
>
> >> - use replication slots for backingup wal on the backup server.
> > Another good feature.  We have not added it yet because pgBackRest was
> > originally written for very high-volume clusters (100K+ WAL per day) and
> > our parallel async feature answers that need much better.  We recommend
> > a replicated standby for more update-to-date data.
>
> Every N minutes you copy the WAL files to the backup server?
>
>
> --
> Angular momentum makes the world go 'round.
>
>

Re: Barman versus pgBackRest

[Ron]

On 09/04/2018 07:14 AM, Thomas Poty wrote:

> Do you just change the IP address of the "restore target"?
Do you expect a typical restore command?


I'm investigating barman and pgBackRest to replace our exitsing NetBackup 
system, so don't know what you mean by "typical restore command".


Here are our typical use cases:

1. If my barman backup server has full backups, diffs and WALs for database 
server MAIN_PG_SERVER, which hosts databases D1, D2 and D3, how much work is 
it to do a PITR restore of D2 to a *different* Pg server?


2. Can I restore an older copy of database D2 to MAIN_PG_SERVER, *giving it 
a new name* (so that now there would be databases D1, D2, D3 *and D2_OLD*)? 
That's pretty trivial on SQL Server, and something I've had to do before so 
the operations staff can research a problem.)


Thanks

--
Angular momentum makes the world go 'round.

psqlODBC

[Kumar, Virendra]

Hi Community,

We are facing some problem with using psqlODBC driver connecting to 
postgres-xl. Whom should we redirect our queries?
Basically, when using load balancing across cluster we are seeing below error 
and the process is failing:
--
"AutomationException: Underlying DBMS error[ERROR: prepared statement 
\"sde_1535573518_38_9763483\" does not exist::SQLSTATE=26000]"

Regards,
Virendra



This message is intended only for the use of the addressee and may contain
information that is PRIVILEGED AND CONFIDENTIAL.

If you are not the intended recipient, you are hereby notified that any
dissemination of this communication is strictly prohibited. If you have
received this communication in error, please erase all copies of the message
and its attachments and notify the sender immediately. Thank you.

Re: Barman versus pgBackRest

[Thomas Poty]

Your problem looks like this one ;-)
https://groups.google.com/forum/#!topic/pgbarman/kXcEpSLhw8w
answer may help

Physical backup/restore operates on a whole cluster...

Le mar. 4 sept. 2018 à 14:47, Ron  a écrit :

> On 09/04/2018 07:14 AM, Thomas Poty wrote:
>
> > Do you just change the IP address of the "restore target"?
> Do you expect a typical restore command?
>
>
> I'm investigating barman and pgBackRest to replace our exitsing NetBackup
> system, so don't know what you mean by "typical restore command".
>
> Here are our typical use cases:
>
> 1. If my barman backup server has full backups, diffs and WALs for
> database server MAIN_PG_SERVER, which hosts databases D1, D2 and D3, how
> much work is it to do a PITR restore of D2 to a *different* Pg server?
>
> 2. Can I restore an older copy of database D2 to MAIN_PG_SERVER, *giving
> it a new name* (so that now there would be databases D1, D2, D3 *and
> D2_OLD*)?  That's pretty trivial on SQL Server, and something I've had to
> do before so the operations staff can research a problem.)
>
> Thanks
>
> --
> Angular momentum makes the world go 'round.
>

Re: psqlODBC

[Adrian Klaver]

On 09/04/2018 06:38 AM, Kumar, Virendra wrote:

Hi Community,

We are facing some problem with using psqlODBC driver connecting to 
postgres-xl. Whom should we redirect our queries?


https://www.postgresql.org/list/pgsql-odbc/



Basically, when using load balancing across cluster we are seeing below 
error and the process is failing:


­--

“AutomationException: Underlying DBMS error[ERROR: prepared statement 
\"sde_1535573518_38_9763483\" does not exist::SQLSTATE=26000]”


Regards,

Virendra





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

Re: Barman versus pgBackRest

[Ron]

That was about barman, in the barman group.  This is asking about 
pgbackrest...  :)


So: does pgbackrest have this ability which barman does not have? The 
"--db-include" option seems to indicate that you can restore a single db, 
but does indicate whether or not you can rename it.



On 09/04/2018 08:44 AM, Thomas Poty wrote:

Your problem looks like this one ;-)
https://groups.google.com/forum/#!topic/pgbarman/kXcEpSLhw8w 


answer may help

Physical backup/restore operates on a whole cluster...

Le mar. 4 sept. 2018 à 14:47, Ron > a écrit :


On 09/04/2018 07:14 AM, Thomas Poty wrote:

> Do you just change the IP address of the "restore target"?
Do you expect a typical restore command?


I'm investigating barman and pgBackRest to replace our exitsing
NetBackup system, so don't know what you mean by "typical restore
command".

Here are our typical use cases:

1. If my barman backup server has full backups, diffs and WALs for
database server MAIN_PG_SERVER, which hosts databases D1, D2 and D3,
how much work is it to do a PITR restore of D2 to a *different* Pg server?

2. Can I restore an older copy of database D2 to MAIN_PG_SERVER,
*giving it a new name* (so that now there would be databases D1, D2,
D3 *and D2_OLD*)?  That's pretty trivial on SQL Server, and something
I've had to do before so the operations staff can research a problem.)

Thanks

-- 
Angular momentum makes the world go 'round.




--
Angular momentum makes the world go 'round.

RE: psqlODBC

[Kumar, Virendra]

Thank you Adrian!

Hi ODBC Project Team,

Would you mind looking into this please?


Regards,
Virendra.

-Original Message-
From: Adrian Klaver [mailto:adrian.kla...@aklaver.com]
Sent: Tuesday, September 04, 2018 9:47 AM
To: Kumar, Virendra; pgsql-general@lists.postgresql.org
Subject: Re: psqlODBC

On 09/04/2018 06:38 AM, Kumar, Virendra wrote:
> Hi Community,
>
> We are facing some problem with using psqlODBC driver connecting to
> postgres-xl. Whom should we redirect our queries?

https://www.postgresql.org/list/pgsql-odbc/

>
> Basically, when using load balancing across cluster we are seeing below
> error and the process is failing:
>
> --
>
> "AutomationException: Underlying DBMS error[ERROR: prepared statement
> \"sde_1535573518_38_9763483\" does not exist::SQLSTATE=26000]"
>
> Regards,
>
> Virendra
>



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




This message is intended only for the use of the addressee and may contain
information that is PRIVILEGED AND CONFIDENTIAL.

If you are not the intended recipient, you are hereby notified that any
dissemination of this communication is strictly prohibited. If you have
received this communication in error, please erase all copies of the message
and its attachments and notify the sender immediately. Thank you.

Re: Barman versus pgBackRest

[Joshua D. Drake]

On 09/04/2018 07:14 AM, Ron wrote:


That was about barman, in the barman group.  This is asking about 
pgbackrest...  :)


So: does pgbackrest have this ability which barman does not have? The 
"--db-include" option seems to indicate that you can restore a single 
db, but does indicate whether or not you can rename it.


https://pgbackrest.org/configuration.html#section-restore/option-db-include

JD


--
Command Prompt, Inc. || http://the.postgres.company/ || @cmdpromptinc
***  A fault and talent of mine is to tell it exactly how it is.  ***
PostgreSQL centered full stack support, consulting and development.
Advocate: @amplifypostgres || Learn: https://postgresconf.org
* Unless otherwise stated, opinions are my own.   *

Re: Barman versus pgBackRest

[Ron]

On 09/04/2018 09:24 AM, Joshua D. Drake wrote:

On 09/04/2018 07:14 AM, Ron wrote:


That was about barman, in the barman group.  This is asking about 
pgbackrest...  :)


So: does pgbackrest have this ability which barman does not have? The 
"--db-include" option seems to indicate that you can restore a single db, 
but does indicate whether or not you can rename it.


https://pgbackrest.org/configuration.html#section-restore/option-db-include



Which implies that you can't do it?

(Postgres backup/restore capabilities are quite limited, which is disapointing.)

--
Angular momentum makes the world go 'round.

increasing HA

[Thomas Poty]

Hello,

I am looking after some advice about solution allowing to increase High
availability?

Here is a bit of context :

I have an Master-Slave architecture
 - 1 master
 - 2 asynchronous slaves using replication slot
 - backup is made with barman using replication slot
 - Wal archiving is done towards barman server

I think 3 axes could be improved ( I am totaly novice with these):

- using of a proxy
 I found HAproxy.
 Could you advice any others solutions to explore or share your experience?

- using an automatick failover
 I found PAF
 Could you advice any others solutions to explore or share your experience?

- using a tool for fencing a failing node
Ideally, I aimagine to disable network traffic in I/O to prevent client
connecting and exchange between backup server failling server + on
postgesql server disable automatic restart of the service.
Could you share you experience about it?

- Maybe an other axe to explore ?

Thank you

Thomas

RE: increasing HA

[ROS Didier]

Hi
   I have made a lot of PostgreSQL High Availability tests (more 
than 20 by solution) and the two following products respond well to the need :

(1)Repmgr (2ndQuadrant)

(2)Pglookout (aiven)

About PAF, the product is hard to install and set up . It need a linux cluster 
and a system engineers team to use it.

Best Regards

[cid:image002.png@01D14E0E.8515EB90]


Didier ROS
Expertise SGBD
DS IT/IT DMA/Solutions Groupe EDF/Expertise Applicative - SGBD
Nanterre Picasso - E2 565D (aile nord-est)
32 Avenue Pablo Picasso
92000 Nanterre


De : thomas.p...@gmail.com [mailto:thomas.p...@gmail.com]
Envoyé : mardi 4 septembre 2018 16:59
À : pgsql-general@lists.postgresql.org
Objet : increasing HA

Hello,

I am looking after some advice about solution allowing to increase High 
availability?

Here is a bit of context :

I have an Master-Slave architecture
 - 1 master
 - 2 asynchronous slaves using replication slot
 - backup is made with barman using replication slot
 - Wal archiving is done towards barman server

I think 3 axes could be improved ( I am totaly novice with these):

- using of a proxy
 I found HAproxy.
 Could you advice any others solutions to explore or share your experience?
- using an automatick failover
 I found PAF
 Could you advice any others solutions to explore or share your experience?
- using a tool for fencing a failing node
Ideally, I aimagine to disable network traffic in I/O to prevent client 
connecting and exchange between backup server failling server + on postgesql 
server disable automatic restart of the service.
Could you share you experience about it?

- Maybe an other axe to explore ?

Thank you

Thomas



Ce message et toutes les pièces jointes (ci-après le 'Message') sont établis à 
l'intention exclusive des destinataires et les informations qui y figurent sont 
strictement confidentielles. Toute utilisation de ce Message non conforme à sa 
destination, toute diffusion ou toute publication totale ou partielle, est 
interdite sauf autorisation expresse.

Si vous n'êtes pas le destinataire de ce Message, il vous est interdit de le 
copier, de le faire suivre, de le divulguer ou d'en utiliser tout ou partie. Si 
vous avez reçu ce Message par erreur, merci de le supprimer de votre système, 
ainsi que toutes ses copies, et de n'en garder aucune trace sur quelque support 
que ce soit. Nous vous remercions également d'en avertir immédiatement 
l'expéditeur par retour du message.

Il est impossible de garantir que les communications par messagerie 
électronique arrivent en temps utile, sont sécurisées ou dénuées de toute 
erreur ou virus.


This message and any attachments (the 'Message') are intended solely for the 
addressees. The information contained in this Message is confidential. Any use 
of information contained in this Message not in accord with its purpose, any 
dissemination or disclosure, either whole or partial, is prohibited except 
formal approval.

If you are not the addressee, you may not copy, forward, disclose or use any 
part of it. If you have received this message in error, please delete it and 
all copies from your system and notify the sender immediately by return message.

E-mail communication cannot be guaranteed to be timely secure, error or 
virus-free.

Re: Barman versus pgBackRest

[Joshua D. Drake]

On 09/04/2018 07:52 AM, Ron wrote:

On 09/04/2018 09:24 AM, Joshua D. Drake wrote:

On 09/04/2018 07:14 AM, Ron wrote:


That was about barman, in the barman group.  This is asking about 
pgbackrest...  :)


So: does pgbackrest have this ability which barman does not have? 
The "--db-include" option seems to indicate that you can restore a 
single db, but does indicate whether or not you can rename it.


https://pgbackrest.org/configuration.html#section-restore/option-db-include 





Which implies that you can't do it?


You can restore a single database and then issue a simple ALTER DATABASE 
command to change the DB name.




(Postgres backup/restore capabilities are quite limited, which is 
disapointing.)


Not sure I agree with that. If you want to restore and then rename a DB, 
rename it.


ALTER DATABASE foo RENAME TO bar;

JD



--
Command Prompt, Inc. || http://the.postgres.company/ || @cmdpromptinc
***  A fault and talent of mine is to tell it exactly how it is.  ***
PostgreSQL centered full stack support, consulting and development.
Advocate: @amplifypostgres || Learn: https://postgresconf.org
* Unless otherwise stated, opinions are my own.   *

Re: increasing HA

[Joshua D. Drake]

On 09/04/2018 07:58 AM, Thomas Poty wrote:


- using an automatick failover
 I found PAF
 Could you advice any others solutions to explore or share your 
experience?


LinuxHA or Patroni are the most common we run into.

JD--

Command Prompt, Inc. || http://the.postgres.company/ || @cmdpromptinc
***  A fault and talent of mine is to tell it exactly how it is.  ***
PostgreSQL centered full stack support, consulting and development.
Advocate: @amplifypostgres || Learn: https://postgresconf.org
* Unless otherwise stated, opinions are my own.   *

Re: Barman versus pgBackRest

[Ron]

On 09/04/2018 10:24 AM, Joshua D. Drake wrote:

On 09/04/2018 07:52 AM, Ron wrote:

On 09/04/2018 09:24 AM, Joshua D. Drake wrote:

On 09/04/2018 07:14 AM, Ron wrote:


That was about barman, in the barman group.  This is asking about 
pgbackrest...  :)


So: does pgbackrest have this ability which barman does not have? The 
"--db-include" option seems to indicate that you can restore a single 
db, but does indicate whether or not you can rename it.


https://pgbackrest.org/configuration.html#section-restore/option-db-include



Which implies that you can't do it?


You can restore a single database and then issue a simple ALTER DATABASE 
command to change the DB name.




(Postgres backup/restore capabilities are quite limited, which is 
disapointing.)


Not sure I agree with that. If you want to restore and then rename a DB, 
rename it.


ALTER DATABASE foo RENAME TO bar;


But restoring an old "foo" overwrites the existing "foo".  On SQL Server 
databases, we occasionally need to restore an old foo backup "foo_old" along 
side production foo.



--
Angular momentum makes the world go 'round.

Re: Barman versus pgBackRest

[David Steele]

On 9/4/18 11:24 AM, Joshua D. Drake wrote:
> On 09/04/2018 07:52 AM, Ron wrote:
>> On 09/04/2018 09:24 AM, Joshua D. Drake wrote:
>>> On 09/04/2018 07:14 AM, Ron wrote:

 That was about barman, in the barman group.  This is asking about
 pgbackrest...  :)

 So: does pgbackrest have this ability which barman does not have?
 The "--db-include" option seems to indicate that you can restore a
 single db, but does indicate whether or not you can rename it.
>>>
>>> https://pgbackrest.org/configuration.html#section-restore/option-db-include
>>>
>>>
>>
>> Which implies that you can't do it?
> 
> You can restore a single database and then issue a simple ALTER DATABASE
> command to change the DB name.

This will work, but I don't think it's what Ron is getting at.

To be clear, it is not possible to restore a database into an *existing*
cluster using pgBackRest selective restore.  This is a limitation of
PostgreSQL file-level backups.

To do what Ron wants you would need to restore it to a new cluster, then
use pg_dump to logically dump and restore it to whatever cluster you
want it in.  This still saves time since there is less to restore but is
obviously not ideal.

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

Re: Barman versus pgBackRest

[Ron]

On 09/04/2018 10:51 AM, David Steele wrote:
[snip]

This will work, but I don't think it's what Ron is getting at.

To be clear, it is not possible to restore a database into an *existing*
cluster using pgBackRest selective restore.  This is a limitation of
PostgreSQL file-level backups.

To do what Ron wants you would need to restore it to a new cluster, then
use pg_dump to logically dump and restore it to whatever cluster you
want it in.  This still saves time since there is less to restore but is
obviously not ideal.


That's exactly what I'm referring to.

Presumably I could restore it to a new cluster on the same VM via initdb on 
a different port and PGDATA directory?


--
Angular momentum makes the world go 'round.

Re: Barman versus pgBackRest

[David Steele]

On 9/4/18 11:55 AM, Ron wrote:
> On 09/04/2018 10:51 AM, David Steele wrote:
> [snip]
>> This will work, but I don't think it's what Ron is getting at.
>>
>> To be clear, it is not possible to restore a database into an *existing*
>> cluster using pgBackRest selective restore.  This is a limitation of
>> PostgreSQL file-level backups.
>>
>> To do what Ron wants you would need to restore it to a new cluster, then
>> use pg_dump to logically dump and restore it to whatever cluster you
>> want it in.  This still saves time since there is less to restore but is
>> obviously not ideal.
> 
> That's exactly what I'm referring to.
> 
> Presumably I could restore it to a new cluster on the same VM via initdb
> on a different port and PGDATA directory?

Definitely.  No need to initdb since all the required files will be
restored by pgBackRest.  You'll just need to create an empty directory
to restore into.

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

Re: Barman versus pgBackRest

[Joshua D. Drake]

On 09/04/2018 08:55 AM, Ron wrote:

On 09/04/2018 10:51 AM, David Steele wrote:
[snip]

This will work, but I don't think it's what Ron is getting at.

To be clear, it is not possible to restore a database into an *existing*
cluster using pgBackRest selective restore.  This is a limitation of
PostgreSQL file-level backups.

To do what Ron wants you would need to restore it to a new cluster, then
use pg_dump to logically dump and restore it to whatever cluster you
want it in.  This still saves time since there is less to restore but is
obviously not ideal.


That's exactly what I'm referring to.

Presumably I could restore it to a new cluster on the same VM via 
initdb on a different port and PGDATA directory?


Yes.

jD


--
Command Prompt, Inc. || http://the.postgres.company/ || @cmdpromptinc
***  A fault and talent of mine is to tell it exactly how it is.  ***
PostgreSQL centered full stack support, consulting and development.
Advocate: @amplifypostgres || Learn: https://postgresconf.org
* Unless otherwise stated, opinions are my own.   *

Re: Barman versus pgBackRest

[David Steele]

Hi Ron,

On 9/4/18 7:41 AM, Ron wrote:
> On 03/09/2018 08:56 AM, David Steele wrote:
> [snip]
>>> About pgBarman, I like :
>>> - be able restore on a remote server from the backup server
>> This a good feature, and one that has been requested for pgBackRest. You
>> can do this fairly trivially with ssh, however, so it generally hasn't
>> been a big deal for people.  Is there a particular reason you need this
>> feature?
> 
> (Sorry to dredge up this old thread.)
> 
> Do you just change the IP address of the "restore target"?

[I'll assume you wanted to hear about pgBackRest here since we discussed
it down thread.]

Generally restores are done from the database server, but if you want to
run a restore from the backup server you can run it via ssh:

ssh user@pg-server pgbackrest [...]

> 
>>> - use replication slots for backingup wal on the backup server.
>> Another good feature.  We have not added it yet because pgBackRest was
>> originally written for very high-volume clusters (100K+ WAL per day) and
>> our parallel async feature answers that need much better.  We recommend
>> a replicated standby for more update-to-date data.
> 
> Every N minutes you copy the WAL files to the backup server?
> 
> 

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

Re: Barman versus pgBackRest

[David Steele]

On 9/4/18 7:41 AM, Ron wrote:
> On 03/09/2018 08:56 AM, David Steele wrote:
> 
>>> - use replication slots for backingup wal on the backup server.
>> Another good feature.  We have not added it yet because pgBackRest was
>> originally written for very high-volume clusters (100K+ WAL per day) and
>> our parallel async feature answers that need much better.  We recommend
>> a replicated standby for more update-to-date data.
> 
> Every N minutes you copy the WAL files to the backup server?

[Accidentally hit send on the previous post, here's the rest...]

WAL segments are transferred whenever Postgres indicates that a segment
is finished via the archive_command.  Async archiving "looks ahead" to
find WAL segments that are ready to archive.

You can use archive_timeout to force Postgres to push a WAL segment
every N seconds for clusters that have idle time.


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

PostgreSQL: Copy from File missing data error

[Holly Gibons]

I'm using PostgreSQL 9.0 via pgAdmin III

I'm trying to build a PostgreSQL/PostGIS database using Entire country
files dataset  but I'm
getting missing data error

I'm wondering if the copy command is affected by diacritics or I've not set
the database up properly
Created a new database with UTF8 encoding

I built the table schema based on the given format
 (but using type
text for NM_MODIFY_DATE not varchar,  having these as dates didn't make a
difference)

I used large text viewer to open the uncompressed countries.txt file and
copied the top 5 rows into a test file

Using   PostgreSQL Copy this test file imported correctly so I know my
schema is correct

copy my_table(List of columns ) from '\\Path\\To\\TestFile.txt' WITH
delimiter E'\t' csv HEADER;

However when I tried to ingest the larger  countries.txt (2.9GB) file I get
an error "missing data" for column xyz at line 12345 (Last column with
 data in that row, NM_MODIFY_DATE)

Using large text viewer again I located this line and copied together with
proceeding and following lines into my test file and tried the copy again
but get the same error

I opened the test file in excel to  see if there is columns  missing
although not every row has data in each column they do  all match

the problem row has UFI -3373955 & UNI 6329083

I don't know if this is relevant but looking at the database properties ,
in pgAdmin, the 'collection' &  'Character type' are both set as
"English_United Kingdom, 1252 " I  didn't set this and creating a  new DB
the options are "C",  "English_United Kingdom, 1252 " or "POSIX"

Could someone suggest what I'm doing wrong?
Thank you

Re: PostgreSQL: Copy from File missing data error

[Ron]

You might want to try pg_bulkload, and have it kick out malformed rows.

It's packaged for RHEL6 and above, plus various other distros.

On 09/04/2018 01:13 PM, Holly Gibons wrote:

I'm using PostgreSQL 9.0 via pgAdmin III

I'm trying to build a PostgreSQL/PostGIS database using Entire country 
files dataset  but I'm 
getting missing data error


I'm wondering if the copy command is affected by diacritics or I've not 
set the database up properly

Created a new database with UTF8 encoding

I built the table schema based on the given format 
 (but using type 
text for NM_MODIFY_DATE not varchar,  having these as dates didn't make a 
difference)


I used large text viewer to open the uncompressed countries.txt file and 
copied the top 5 rows into a test file


Using   PostgreSQL Copy this test file imported correctly so I know my 
schema is correct
|copy my_table(List of columns ) from '\\Path\\To\\TestFile.txt' WITH 
delimiter E'\t' csv HEADER; |
However when I tried to ingest the larger  countries.txt (2.9GB) file I 
get an error "missing data" for column xyz at line 12345 (Last column with 
 data in that row, NM_MODIFY_DATE)


Using large text viewer again I located this line and copied together with 
proceeding and following lines into my test file and tried the copy again 
but get the same error


I opened the test file in excel to  see if there is columns  missing 
although not every row has data in each column they do  all match


the problem row has UFI -3373955 & UNI 6329083

I don't know if this is relevant but looking at the database properties , 
in pgAdmin, the 'collection' &  'Character type' are both set as 
"English_United Kingdom, 1252 " I  didn't set this and creating a  new DB 
the options are "C",  "English_United Kingdom, 1252 " or "POSIX"


Could someone suggest what I'm doing wrong?
Thank you


--
Angular momentum makes the world go 'round.

Re: PostgreSQL: Copy from File missing data error

[Dimitri Maziuk]

On 09/04/2018 01:13 PM, Holly Gibons wrote:

> Could someone suggest what I'm doing wrong?

There's probably a magic character that is not escaped properly
somewhere before the error location. Exactly how to find them all in a
2GB+ file is another question: e.g. if you know python you could play
with csv.Reader and unicode/bytes encode/decode...

-- 
Dimitri Maziuk
Programmer/sysadmin
BioMagResBank, UW-Madison -- http://www.bmrb.wisc.edu



signature.asc
Description: OpenPGP digital signature

scram-sha-256 authentication broken in FIPS mode

[Alessandro Gherardi]

It looks like scram-sha-256 doesn't work when postgres is linked against 
FIPS-enabled OpenSSL and FIPS mode is turned on.

Specifically, all login attempts fail with an OpenSSL error saying something 
along the lines of "Low level API call to digest SHA256 forbidden in fips mode".
I think this issue could be solved by refactoring the code in sha2_openssl.c to 
use the OpenSSL EVP interface (see 
https://wiki.openssl.org/index.php/EVP_Message_Digests ).
Any thoughts? Is this a known issue?
Thank you in advance.Alessandro

Re: scram-sha-256 authentication broken in FIPS mode

[Michael Paquier]

On Wed, Sep 05, 2018 at 03:29:31AM +, Alessandro Gherardi wrote:
> It looks like scram-sha-256 doesn't work when postgres is linked
> against FIPS-enabled OpenSSL and FIPS mode is turned on.
> 
> Specifically, all login attempts fail with an OpenSSL error saying
> something along the lines of "Low level API call to digest SHA256
> forbidden in fips mode".

The error comes from libc, right?  Postgres can of course be configured
to work with FIPS without patching it, it just needs to be enabled
system-wide, which is what RedHat does, and what you are doing I guess?

> I think this issue could be solved by refactoring the code in
> sha2_openssl.c to use the OpenSSL EVP interface
> (see https://wiki.openssl.org/index.php/EVP_Message_Digests ). 
> Any thoughts? Is this a known issue?

This report is the first of this kind since Postgres 10, which is where
the SHA2 interface for OpenSSL has been introduced.  So likely we'd need
to look into that more deeply..  This has the strong smell of a bug.  If
your system is new enough, you should have sha256() & co as system
functions, so you would see the failure as well?  The regression tests
would have likely complained.
--
Michael


signature.asc
Description: PGP signature