[GENERAL] PG 9.4.4 issue on French Windows 32 bits

[Thierry Hauchard]

Hy,

We can't upgrade our customers from 8.4 to 9.4.4 if they have a 32 bits 
server machine (Windows 2008, 2007...).

There is no problem with PG 64 bits.

When restoring from backup (created from 8.4 database with PG_Dump 
9.4.4), the log shows errors about UTF like :
2015-07-07 17:03:35 CEST ERREUR:  séquence d'octets invalide pour 
l'encodage « UTF8 » : 0xf4 0x6c 0x65 0x20


Using our application, using win_1252 client_encoding,  get similar 
error if we try to update record with a "\" in any string :


UPDATE test_table SET str_field = '\\' WHERE id = 75160909
-> ERROR:  invalid byte sequence for encoding "UTF8": 0xee 0x6e 0x65

The same query work from PG_Admin (UTF8 encoding)

Our technicien install PG cluster exactly the same way.
We have tried on 3 differents 32 bits machine, same issue
No issue on 64bits machine (majority of our customers). All works perfectly.
We have never had any problem with PG 8.4 for long years

Is there a way to turn-over ?
Or need to wait upgrade ?

Thanks,
Thierry Hauchard
Gesteam


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

Re: [GENERAL] [pg_hba.conf] publish own Python application using PostgreSQL

[Karsten Hilbert]

On Tue, Jul 07, 2015 at 06:57:45AM -0500, John McKown wrote:

> >>> >at a bare minimum, a database administrator needs to create database
> >>> >roles (users) and databases for an app like yours.
> >>>
> >> The admin don't need to create the db. It is done by the application
> >> (sqlalchemy-utils on Python3) itself.
> >>
> >
> > an application should not have the privileges to do that.   you don't run
> > your apps as 'root', do you?   why would you run them as a database
> > administrator ?
> 
> 
> ​Trigger Warning (Thanks, Mallard Fillmore)
> 
> I agree with you on this. If I were a customer and some vendor said: "Oh
> yes, to run our product, you must configure your multi-user data base to
> disable passwords and run it as a DBA so that it can make schema changes on
> the fly", then I'd simply say "no sale". Of course, in regards to the
> schema, it would be proper to document what the DBA needs to do to set up
> the data base with the proper tables and other items.

In fact, an app might have an option to emit a script for
the DBA to run. Or even offer to run it for the DBA given
proper credentials are provided on the spot.

Karsten Hilbert
-- 
GPG key ID E4071346 @ eu.pool.sks-keyservers.net
E167 67FD A291 2BEA 73BD  4537 78B9 A9F9 E407 1346


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

[GENERAL] Problem with ALTER TYPE, Indexes and cast

[Marc Mamin]

Hello,

I plan to replace some btree indexes through btree_gin on some timed 
tables(e.g. monthly tables)
For this, I first need to change the data type from character(n) to varchar, 
but I can't afford it on historical tables as this would be too time consuming, 
so only new tables should get the varchar type.

Now I have generated queries that include cast information in order to ensure 
that the indexes get used.

e.g.:  WHERE month1.foo = cast('XY' as character(2))

with mixed type, this should become something like:

 SELECT ... FROM month1
 WHERE month1.foo = cast('XY' as character(2))
 UNION ALL
 SELECT... FROM month2
 WHERE month2.foo = cast('XY' as varchar)
 
 which is quite complicated to resolve in our "query builder framework"
 
 
 There seems to be no way to have dynamic casting, something like:
 
  WHERE month2.foo = cast('XY' as 'month2.foo'::regtype)
  
 Is there a way for it ?
 
 
 regards,
 Marc Mamin


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

Re: [GENERAL] [pg_hba.conf] publish own Python application using PostgreSQL

[John McKown]

On Wed, Jul 8, 2015 at 3:55 AM, Karsten Hilbert 
wrote:

> On Tue, Jul 07, 2015 at 06:57:45AM -0500, John McKown wrote:
>
> > >>> >at a bare minimum, a database administrator needs to create database
> > >>> >roles (users) and databases for an app like yours.
> > >>>
> > >> The admin don't need to create the db. It is done by the application
> > >> (sqlalchemy-utils on Python3) itself.
> > >>
> > >
> > > an application should not have the privileges to do that.   you don't
> run
> > > your apps as 'root', do you?   why would you run them as a database
> > > administrator ?
> >
> >
> > ​Trigger Warning (Thanks, Mallard Fillmore)
> >
> > I agree with you on this. If I were a customer and some vendor said: "Oh
> > yes, to run our product, you must configure your multi-user data base to
> > disable passwords and run it as a DBA so that it can make schema changes
> on
> > the fly", then I'd simply say "no sale". Of course, in regards to the
> > schema, it would be proper to document what the DBA needs to do to set up
> > the data base with the proper tables and other items.
>
> In fact, an app might have an option to emit a script for
> the DBA to run. Or even offer to run it for the DBA given
> proper credentials are provided on the spot.
>

​Yes, that's even better. Documentation to say what to do and why, and a
way to generate a script which the DBA can review, approve, & run is an
excellent way to do this.​



>
> Karsten Hilbert
>
>
-- 

Schrodinger's backup: The condition of any backup is unknown until a
restore is attempted.

Yoda of Borg, we are. Futile, resistance is, yes. Assimilated, you will be.

He's about as useful as a wax frying pan.

10 to the 12th power microphones = 1 Megaphone

Maranatha! <><
John McKown

Re: [GENERAL] 9.4 on Ubuntu 15.04: ENETUNREACH error?

[Adrian Klaver]

On 07/07/2015 11:21 PM, Chas. Munat wrote:

I recently upgraded to Ubuntu 15.04 with PostgreSQL 9.4.4 with uuids and
plv8 coffeescript extensions. I can access the database via psql. My web
application uses node.js via pg-bricks. It works perfectly on my Mac dev
machine.


The Mac has it's own database or is connecting to the one on the Ubuntu 
machine?




On the server, I get this error:

preload failed { [Error: connect ENETUNREACH 127.0.0.0:5432 - Local
(0.0.0.0:0)]
   code: 'ENETUNREACH',
   errno: 'ENETUNREACH',
   syscall: 'connect',
   address: '127.0.0.0',
   port: 5432 }

(Preload is a script in my app that does multiple queries via pg-bricks.)

I have turned off ufw, set pg_hba.conf to TRUST, done everything I can
think of -- no  joy.


Are you having IPv4/v6 issues?

Have you looked at/changed the settings for IPv6 connections in pg_hba.conf?



Any ideas why this might be happening?




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


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

Re: [GENERAL] PG 9.4.4 issue on French Windows 32 bits

[Adrian Klaver]

On 07/08/2015 01:04 AM, Thierry Hauchard wrote:

Hy,

We can't upgrade our customers from 8.4 to 9.4.4 if they have a 32 bits
server machine (Windows 2008, 2007...).
There is no problem with PG 64 bits.


Except below you say only for majority of 64bit machines, so are the 
problems when they occur the same as you are seeing on the 32bit machines?




When restoring from backup (created from 8.4 database with PG_Dump
9.4.4), the log shows errors about UTF like :
2015-07-07 17:03:35 CEST ERREUR:  séquence d'octets invalide pour
l'encodage « UTF8 » : 0xf4 0x6c 0x65 0x20


So what is the database encoding in the 8.4 instances?



Using our application, using win_1252 client_encoding,  get similar
error if we try to update record with a "\" in any string :


So what happens if you use psql and do:

\encoding win_1252

and then run the query below?



UPDATE test_table SET str_field = '\\' WHERE id = 75160909
-> ERROR:  invalid byte sequence for encoding "UTF8": 0xee 0x6e 0x65

The same query work from PG_Admin (UTF8 encoding)


Make me think you had database in an encoding other then UTF8 previously.



Our technicien install PG cluster exactly the same way.
We have tried on 3 differents 32 bits machine, same issue
No issue on 64bits machine (majority of our customers). All works
perfectly.
We have never had any problem with PG 8.4 for long years

Is there a way to turn-over ?
Or need to wait upgrade ?

Thanks,
Thierry Hauchard
Gesteam





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


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

Re: [GENERAL] 9.4 on Ubuntu 15.04: ENETUNREACH error?

[Tom Lane]

"Chas. Munat"  writes:
> I recently upgraded to Ubuntu 15.04 with PostgreSQL 9.4.4 with uuids and 
> plv8 coffeescript extensions. I can access the database via psql. My web 
> application uses node.js via pg-bricks. It works perfectly on my Mac dev 
> machine.

> On the server, I get this error:

> preload failed { [Error: connect ENETUNREACH 127.0.0.0:5432 - Local 
> (0.0.0.0:0)]
>code: 'ENETUNREACH',
>errno: 'ENETUNREACH',
>syscall: 'connect',
>address: '127.0.0.0',
>port: 5432 }

Something in your configuration is trying to connect to 127.0.0.0,
which should be expected to fail.  Presumably 127.0.0.1 (the standard
IPv4 loopback address) was meant.  Look for typos in whatever is
determining the address that the client tries to connect to.

regards, tom lane


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

Re: [GENERAL] Problem with ALTER TYPE, Indexes and cast

[Tom Lane]

Marc Mamin  writes:
> Now I have generated queries that include cast information in order to ensure 
> that the indexes get used.

> e.g.:  WHERE month1.foo = cast('XY' as character(2))

> with mixed type, this should become something like:

>  SELECT ... FROM month1
>  WHERE month1.foo = cast('XY' as character(2))
>  UNION ALL
>  SELECT... FROM month2
>  WHERE month2.foo = cast('XY' as varchar)
 
>  which is quite complicated to resolve in our "query builder framework"
 
 >  There seems to be no way to have dynamic casting, something like:
 
>   WHERE month2.foo = cast('XY' as 'month2.foo'::regtype)
  
>  Is there a way for it ?

If the comparison values are always string literals, then you should just
drop the casts altogether, ie

  WHERE month2.foo = 'XY'

In this sort of situation the literal's type is preferentially resolved as
being the same as whatever it's being compared to.

regards, tom lane


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

Re: [GENERAL] Problem with ALTER TYPE, Indexes and cast

[Marc Mamin]

> -Original Message-
> From: Tom Lane [mailto:t...@sss.pgh.pa.us]
> Sent: Mittwoch, 8. Juli 2015 15:44
> To: Marc Mamin
> Cc: pgsql-general@postgresql.org
> Subject: Re: [GENERAL] Problem with ALTER TYPE, Indexes and cast
> 
> Marc Mamin  writes:
> > Now I have generated queries that include cast information in order
> to ensure that the indexes get used.
> 
> > e.g.:  WHERE month1.foo = cast('XY' as character(2))
> 
> > with mixed type, this should become something like:
> 
> >  SELECT ... FROM month1
> >  WHERE month1.foo = cast('XY' as character(2))  UNION ALL  SELECT...
> > FROM month2  WHERE month2.foo = cast('XY' as varchar)
> 
> >  which is quite complicated to resolve in our "query builder
> framework"
> 
>  >  There seems to be no way to have dynamic casting, something like:
> 
> >   WHERE month2.foo = cast('XY' as 'month2.foo'::regtype)
> 
> >  Is there a way for it ?
> 
> If the comparison values are always string literals, then you should
> just drop the casts altogether, ie
> 
>   WHERE month2.foo = 'XY'
> 
> In this sort of situation the literal's type is preferentially resolved
> as being the same as whatever it's being compared to.

I had to dig a bit to find out why I was using the cast.
My issue is that I first clean the literal at some places with text returning 
functions.
The index won't get used when comparing to text:

create temp table idtest (c character(8));
insert into idtest select cast(s as character(8)) from 
generate_series(1,3)s;
create index idtest_c on idtest(c);
analyze  idtest;

explain analyze select * from idtest where c = substring 
(trim('1234567890abc') for 8)

Seq Scan on idtest  (cost=0.00..816.99 rows=200 width=9) (actual 
time=20.302..20.302 rows=0 loops=1)
  Filter: ((c)::text = '12345678'::text)


I can easily get rid of the cast while preprocessing the literal before 
injecting it in the query though.

regards,
Marc Mamin




>   regards, tom lane


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

Re: [GENERAL] PG 9.4.4 issue on French Windows 32 bits

[Thierry Hauchard]

There is NO problem on 64 bits machines with PG 64 bits.
(We have not try PG 32 bits on 64 bits machine.)
There IS problem on all 32 bits machine with PG 32 bits

All databases are encoded on PG in UTF8

We set that on all connexions :
SET CLIENT_ENCODING TO WIN1252;
SET bytea_output TO escape;
SET standard_conforming_strings TO false;

I don't use psql. So i don't anderstand when i must type  "\encoding 
win1252".
if i use psql on the test 9.4.4 database and type "\encoding win1252", 
nothing happens

If then i try a simple query updating string with "\\", value is stored
If i do same thing with some accentuated char : "\\éà", there are NOT 
updated in database, and no error (and nothing in PG log)

If i do that from PG_Admin, it works
If i type "SET CLIENT_ENCODING TO WIN1252;", psql return error : invalid 
byte sequence for encoding "UTF8": 0xe9 0x71 0x75

If i do that from PG_Admin, it works
If i type "SET CLIENT_ENCODING TO WIN1252"  (without ";"), it works

I don't see any coherent things !  :(

Thanks,
--
Thierry
Gesteam

Le 08/07/2015 15:20, Adrian Klaver a écrit :

On 07/08/2015 01:04 AM, Thierry Hauchard wrote:

Hy,

We can't upgrade our customers from 8.4 to 9.4.4 if they have a 32 bits
server machine (Windows 2008, 2007...).
There is no problem with PG 64 bits.


Except below you say only for majority of 64bit machines, so are the 
problems when they occur the same as you are seeing on the 32bit 
machines?




When restoring from backup (created from 8.4 database with PG_Dump
9.4.4), the log shows errors about UTF like :
2015-07-07 17:03:35 CEST ERREUR:  séquence d'octets invalide pour
l'encodage « UTF8 » : 0xf4 0x6c 0x65 0x20


So what is the database encoding in the 8.4 instances?



Using our application, using win_1252 client_encoding,  get similar
error if we try to update record with a "\" in any string :


So what happens if you use psql and do:

\encoding win_1252

and then run the query below?



UPDATE test_table SET str_field = '\\' WHERE id = 75160909
-> ERROR:  invalid byte sequence for encoding "UTF8": 0xee 0x6e 0x65

The same query work from PG_Admin (UTF8 encoding)


Make me think you had database in an encoding other then UTF8 previously.



Our technicien install PG cluster exactly the same way.
We have tried on 3 differents 32 bits machine, same issue
No issue on 64bits machine (majority of our customers). All works
perfectly.
We have never had any problem with PG 8.4 for long years

Is there a way to turn-over ?
Or need to wait upgrade ?

Thanks,
Thierry Hauchard
Gesteam









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

Re: [GENERAL] PG 9.4.4 issue on French Windows 32 bits

[Adrian Klaver]

On 07/08/2015 08:40 AM, Thierry Hauchard wrote:

There is NO problem on 64 bits machines with PG 64 bits.


I saw this line from your previous post:

"No issue on 64bits machine (majority of our customers)."

Realize now that this means the majority of your customers are using 
64bit Windows. I read it as there was no problem on the majority of the 
machines that ran 64bit, which implied there where some 64bit machines 
that has issues. Looking from wrong end of telescope:)



(We have not try PG 32 bits on 64 bits machine.)
There IS problem on all 32 bits machine with PG 32 bits

All databases are encoded on PG in UTF8


Just to be clear the 8.4 versions of the database where set up with UTF8?



We set that on all connexions :
SET CLIENT_ENCODING TO WIN1252;
SET bytea_output TO escape;
SET standard_conforming_strings TO false;


What library are you using to make connections in your application?



I don't use psql. So i don't anderstand when i must type  "\encoding
win1252".
if i use psql on the test 9.4.4 database and type "\encoding win1252",
nothing happens


Actually something does happen:

postgres@production=# \encoding
UTF8
postgres@production=# \encoding win_1252
postgres@production=# \encoding
WIN1252

For more detail see here:
http://www.postgresql.org/docs/9.4/interactive/app-psql.html


If then i try a simple query updating string with "\\", value is stored
If i do same thing with some accentuated char : "\\éà", there are NOT
updated in database, and no error (and nothing in PG log)


Can you show the actual full queries and return values or errors from in 
psql command line?




If i do that from PG_Admin, it works
If i type "SET CLIENT_ENCODING TO WIN1252;", psql return error : invalid
byte sequence for encoding "UTF8": 0xe9 0x71 0x75


The psql in the above confuses me. Are you referring to the command line 
program psql or to the Postgres server?


Generally psql is used to refer to the command line program and Postgres 
or Pg(pg) is used to refer to the server.



If i do that from PG_Admin, it works
If i type "SET CLIENT_ENCODING TO WIN1252"  (without ";"), it works


I do not use pgAdmin enough, but I am guessing it adds the ; when it 
parses the command.




I don't see any coherent things !  :(

Thanks,
--
Thierry
Gesteam

Le 08/07/2015 15:20, Adrian Klaver a écrit :

On 07/08/2015 01:04 AM, Thierry Hauchard wrote:

Hy,

We can't upgrade our customers from 8.4 to 9.4.4 if they have a 32 bits
server machine (Windows 2008, 2007...).
There is no problem with PG 64 bits.


Except below you say only for majority of 64bit machines, so are the
problems when they occur the same as you are seeing on the 32bit
machines?



When restoring from backup (created from 8.4 database with PG_Dump
9.4.4), the log shows errors about UTF like :
2015-07-07 17:03:35 CEST ERREUR:  séquence d'octets invalide pour
l'encodage « UTF8 » : 0xf4 0x6c 0x65 0x20


So what is the database encoding in the 8.4 instances?



Using our application, using win_1252 client_encoding,  get similar
error if we try to update record with a "\" in any string :


So what happens if you use psql and do:

\encoding win_1252

and then run the query below?



UPDATE test_table SET str_field = '\\' WHERE id = 75160909
-> ERROR:  invalid byte sequence for encoding "UTF8": 0xee 0x6e 0x65

The same query work from PG_Admin (UTF8 encoding)


Make me think you had database in an encoding other then UTF8 previously.



Our technicien install PG cluster exactly the same way.
We have tried on 3 differents 32 bits machine, same issue
No issue on 64bits machine (majority of our customers). All works
perfectly.
We have never had any problem with PG 8.4 for long years

Is there a way to turn-over ?
Or need to wait upgrade ?

Thanks,
Thierry Hauchard
Gesteam












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


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

[GENERAL] Dealing with bdr update/update conflicts

[Giles Westwood]

Hi all,

I've been testing bdr over the last few days and trying to simulate
conflicts.

For an update/update conflict I powered down node a and updated on node b.
Then powered down node b and updated node a and powered node b back on.

The situation I'm left in is that replication happens one way and it's
possible for me to get each record to be identical but whenever I try and
update in the other direction it doesn't replicate and I get this entry in
the conflict history log.

conflict_id  | 860
local_node_sysid | 6166345561721046825
local_conflict_xid   | 4990
local_conflict_lsn   | 0/CA06DD98
local_conflict_time  | 2015-07-08 16:30:29.276713+00
object_schema| public
object_name  | table
remote_node_sysid| 6166334043667378995
remote_txid  | 3114
remote_commit_time   | 2015-07-08 15:45:44.999168+00
remote_commit_lsn| 1/4104FF98
conflict_type| update_update
conflict_resolution  | last_update_wins_keep_local
local_tuple  |
{"table_id":1452776,"last_update_id":"xxx","password":"obs","username":"final7","acc_id":1,"last_update_time":"2015-07-08T16:16:34.854137+00:00","make_public":false}
remote_tuple |
{"table_id":1452776,"last_update_id":"xxx","password":"obs","username":"final8","acc_id":1,"last_update_time":"2015-07-08T15:45:44.994954+00:00","make_public":false}
local_tuple_xmin | 4988



-- 
Giles Westwood

Re: [GENERAL] PG 9.4.4 issue on French Windows 32 bits

[Daniel Verite]

Thierry Hauchard wrote:

> When restoring from backup (created from 8.4 database with PG_Dump
> 9.4.4), the log shows errors about UTF like :
> 2015-07-07 17:03:35 CEST ERREUR:  séquence d'octets invalide pour
> l'encodage « UTF8 » : 0xf4 0x6c 0x65 0x20

[...]

> UPDATE test_table SET str_field = '\\' WHERE id = 75160909
> -> ERROR:  invalid byte sequence for encoding "UTF8": 0xee 0x6e 0x65
> 

These sequences of bytes seem to come from LATIN1-encoded
error messages from the backend, translated to french.

0xf4 0x6c 0x65 is "ôle" which could come from "rôle"="role" in
english, a fragment of message that occurs routinely when
restoring a dump granting permissions to roles that don't exist in
the target cluster.

0xee 0x6e 0x65 is "îne" as in "chaîne" which is "string" in french. It's
plausible that the above update, given standard_conforming_strings
to false, produces the translated version of:
  "nonstandard use of \\' in a string literal"
which is:
  "utilisation non standard de \\' dans une chaîne littérale"
where non-surprisingly, the first non US-ASCII sequence is "îne"

See how lc_messages is configured in postgresql.conf.
Presumably it's French_France.1252 ?

If you can live with english messages, set it to C, otherwise
someone more knowledgeable in Windows might suggest a
proper explanation and fix.
Personally I don't understand in the first place how UTF-8
is handled with  '*.1252' locales, as cp1252 seems
incompatible with UTF-8 by definition.


Best regards,
-- 
Daniel
PostgreSQL-powered mail user agent and storage: http://www.manitou-mail.org


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

Re: [GENERAL] PG 9.4.4 issue on French Windows 32 bits

[Adrian Klaver]

On 07/08/2015 08:40 AM, Thierry Hauchard wrote:

There is NO problem on 64 bits machines with PG 64 bits.
(We have not try PG 32 bits on 64 bits machine.)
There IS problem on all 32 bits machine with PG 32 bits


Further thought, are the 32bit and 64bit Windows in the same version of 
Windows or different?




All databases are encoded on PG in UTF8

We set that on all connexions :
SET CLIENT_ENCODING TO WIN1252;
SET bytea_output TO escape;
SET standard_conforming_strings TO false;

I don't use psql. So i don't anderstand when i must type  "\encoding
win1252".
if i use psql on the test 9.4.4 database and type "\encoding win1252",
nothing happens
If then i try a simple query updating string with "\\", value is stored
If i do same thing with some accentuated char : "\\éà", there are NOT
updated in database, and no error (and nothing in PG log)
If i do that from PG_Admin, it works
If i type "SET CLIENT_ENCODING TO WIN1252;", psql return error : invalid
byte sequence for encoding "UTF8": 0xe9 0x71 0x75
If i do that from PG_Admin, it works
If i type "SET CLIENT_ENCODING TO WIN1252"  (without ";"), it works

I don't see any coherent things !  :(

Thanks,
--
Thierry
Gesteam

Le 08/07/2015 15:20, Adrian Klaver a écrit :

On 07/08/2015 01:04 AM, Thierry Hauchard wrote:

Hy,

We can't upgrade our customers from 8.4 to 9.4.4 if they have a 32 bits
server machine (Windows 2008, 2007...).
There is no problem with PG 64 bits.


Except below you say only for majority of 64bit machines, so are the
problems when they occur the same as you are seeing on the 32bit
machines?



When restoring from backup (created from 8.4 database with PG_Dump
9.4.4), the log shows errors about UTF like :
2015-07-07 17:03:35 CEST ERREUR:  séquence d'octets invalide pour
l'encodage « UTF8 » : 0xf4 0x6c 0x65 0x20


So what is the database encoding in the 8.4 instances?



Using our application, using win_1252 client_encoding,  get similar
error if we try to update record with a "\" in any string :


So what happens if you use psql and do:

\encoding win_1252

and then run the query below?



UPDATE test_table SET str_field = '\\' WHERE id = 75160909
-> ERROR:  invalid byte sequence for encoding "UTF8": 0xee 0x6e 0x65

The same query work from PG_Admin (UTF8 encoding)


Make me think you had database in an encoding other then UTF8 previously.



Our technicien install PG cluster exactly the same way.
We have tried on 3 differents 32 bits machine, same issue
No issue on 64bits machine (majority of our customers). All works
perfectly.
We have never had any problem with PG 8.4 for long years

Is there a way to turn-over ?
Or need to wait upgrade ?

Thanks,
Thierry Hauchard
Gesteam












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


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

Re: [GENERAL] PG 9.4.4 issue on French Windows 32 bits

[Bob Lunney]

Thierry,

Please post the output of 

$ psql -l

for the database in question for both the 32-bit and 64-bit servers. That will 
show what encoding was specified when the databases where created.

Also, post the output of 

$ psql   -c “show client_encoding”
$ psql   -c "show all" | grep lc_ 

s'il vous plaît.  Also check if any code is setting client_encoding or any of 
the lc_* options on the fly.

Bob Lunney
Senior Database Engineer
AWeber Communications, LLC
1100 Manor Drive
Chalfont, PA  18914 USA




> On Jul 8, 2015, at 12:48 PM, Daniel Verite  wrote:
> 
>   Thierry Hauchard wrote:
> 
>> When restoring from backup (created from 8.4 database with PG_Dump
>> 9.4.4), the log shows errors about UTF like :
>> 2015-07-07 17:03:35 CEST ERREUR:  séquence d'octets invalide pour
>> l'encodage « UTF8 » : 0xf4 0x6c 0x65 0x20
> 
> [...]
> 
>> UPDATE test_table SET str_field = '\\' WHERE id = 75160909
>> -> ERROR:  invalid byte sequence for encoding "UTF8": 0xee 0x6e 0x65
>> 
> 
> These sequences of bytes seem to come from LATIN1-encoded
> error messages from the backend, translated to french.
> 
> 0xf4 0x6c 0x65 is "ôle" which could come from "rôle"="role" in
> english, a fragment of message that occurs routinely when
> restoring a dump granting permissions to roles that don't exist in
> the target cluster.
> 
> 0xee 0x6e 0x65 is "îne" as in "chaîne" which is "string" in french. It's
> plausible that the above update, given standard_conforming_strings
> to false, produces the translated version of:
>  "nonstandard use of \\' in a string literal"
> which is:
>  "utilisation non standard de \\' dans une chaîne littérale"
> where non-surprisingly, the first non US-ASCII sequence is "îne"
> 
> See how lc_messages is configured in postgresql.conf.
> Presumably it's French_France.1252 ?
> 
> If you can live with english messages, set it to C, otherwise
> someone more knowledgeable in Windows might suggest a
> proper explanation and fix.
> Personally I don't understand in the first place how UTF-8
> is handled with  '*.1252' locales, as cp1252 seems
> incompatible with UTF-8 by definition.
> 
> 
> Best regards,
> -- 
> Daniel
> PostgreSQL-powered mail user agent and storage: http://www.manitou-mail.org
> 
> 
> -- 
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general



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

[GENERAL] Re[2]: [GENERAL] PG 9.4.4 issue on French Windows 32 bits

[Ivan Panchenko]

 Hi, 
which build of PosgreSQL did you use?

Postgres Professional has published today its Windows PostgreSQL installer for 
9.4.4, which solves some strange issues with russian encoding in psql occurring 
in the EDB build.
I'm not sure it can help in your case. 
The installer is at http://postgrespro.ru/windows-en.html


Regards, 
Ivan Panchenko



>Среда,  8 июля 2015, 9:54 -07:00 от Adrian Klaver :
>
>On 07/08/2015 08:40 AM, Thierry Hauchard wrote:
>> There is NO problem on 64 bits machines with PG 64 bits.
>> (We have not try PG 32 bits on 64 bits machine.)
>> There IS problem on all 32 bits machine with PG 32 bits
>
>Further thought, are the 32bit and 64bit Windows in the same version of 
>Windows or different?
>
>>
>> All databases are encoded on PG in UTF8
>>
>> We set that on all connexions :
>> SET CLIENT_ENCODING TO WIN1252;
>> SET bytea_output TO escape;
>> SET standard_conforming_strings TO false;
>>
>> I don't use psql. So i don't anderstand when i must type  "\encoding
>> win1252".
>> if i use psql on the test 9.4.4 database and type "\encoding win1252",
>> nothing happens
>> If then i try a simple query updating string with "\\", value is stored
>> If i do same thing with some accentuated char : "\\éà", there are NOT
>> updated in database, and no error (and nothing in PG log)
>> If i do that from PG_Admin, it works
>> If i type "SET CLIENT_ENCODING TO WIN1252;", psql return error : invalid
>> byte sequence for encoding "UTF8": 0xe9 0x71 0x75
>> If i do that from PG_Admin, it works
>> If i type "SET CLIENT_ENCODING TO WIN1252"  (without ";"), it works
>>
>> I don't see any coherent things !  :(
>>
>> Thanks,
>> --
>> Thierry
>> Gesteam
>>
>> Le 08/07/2015 15:20, Adrian Klaver a écrit :
>>> On 07/08/2015 01:04 AM, Thierry Hauchard wrote:
 Hy,

 We can't upgrade our customers from 8.4 to 9.4.4 if they have a 32 bits
 server machine (Windows 2008, 2007...).
 There is no problem with PG 64 bits.
>>>
>>> Except below you say only for majority of 64bit machines, so are the
>>> problems when they occur the same as you are seeing on the 32bit
>>> machines?
>>>

 When restoring from backup (created from 8.4 database with PG_Dump
 9.4.4), the log shows errors about UTF like :
 2015-07-07 17:03:35 CEST ERREUR:  séquence d'octets invalide pour
 l'encodage « UTF8 » : 0xf4 0x6c 0x65 0x20
>>>
>>> So what is the database encoding in the 8.4 instances?
>>>

 Using our application, using win_1252 client_encoding,  get similar
 error if we try to update record with a "\" in any string :
>>>
>>> So what happens if you use psql and do:
>>>
>>> \encoding win_1252
>>>
>>> and then run the query below?
>>>

 UPDATE test_table SET str_field = '\\' WHERE id = 75160909
 -> ERROR:  invalid byte sequence for encoding "UTF8": 0xee 0x6e 0x65

 The same query work from PG_Admin (UTF8 encoding)
>>>
>>> Make me think you had database in an encoding other then UTF8 previously.
>>>

 Our technicien install PG cluster exactly the same way.
 We have tried on 3 differents 32 bits machine, same issue
 No issue on 64bits machine (majority of our customers). All works
 perfectly.
 We have never had any problem with PG 8.4 for long years

 Is there a way to turn-over ?
 Or need to wait upgrade ?

 Thanks,
 Thierry Hauchard
 Gesteam


>>>
>>>
>>
>>
>>
>
>
>-- 
>Adrian Klaver
>adrian.kla...@aklaver.com
>
>
>-- 
>Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
>To make changes to your subscription:
>http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] encrypt psql password in unix script

[Suresh Raja]

Hi:

I cannot use .pgpass as the password stored here is not encrypted.

can i use a encrypted password from unix shell script.  has anybody ran
into same situation. Wht options do i have.

Thanks,
-SR

Re: [GENERAL] encrypt psql password in unix script

[John R Pierce]

On 7/8/2015 11:34 AM, Suresh Raja wrote:

I cannot use .pgpass as the password stored here is not encrypted.

can i use a encrypted password from unix shell script.  has anybody 
ran into same situation. Wht options do i have.


I believe anywhere you enter a password in postgres, it can be the hash 
instead.


but what security does that gain you?if someone gets your 
encrypted/hashed password, he can still log on.   the pgpass file has to 
be permissions 700, so only YOU (and root) can read it.


if these are LOCAL connections to a pg server on the same machine, you 
can use 'ident' as your authentication, where your unix user is used as 
the postgres username.   or, you can use ssl certificates for 
authentication, this is more complex to setup.



--
john r pierce, recycling bits in santa cruz



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

Re: [GENERAL] [SQL] encrypt psql password in unix script

[Steve Midgley]

My suggestion is to put it in an environment variable and set that variable
from a shell startup script that is secured with permissions. (
http://www.postgresql.org/docs/9.4/static/libpq-envars.html)

If you can't do that, the only other method I've used is to setup Postgres
with Ansible, and store the Pg passwords in an ansible vault, which is
encrypted. Ansible asks for the decrypt key when it runs.

Steve


On Wed, Jul 8, 2015 at 11:34 AM, Suresh Raja 
wrote:

> Hi:
>
> I cannot use .pgpass as the password stored here is not encrypted.
>
> can i use a encrypted password from unix shell script.  has anybody ran
> into same situation. Wht options do i have.
>
> Thanks,
> -SR
>

Re: [GENERAL] encrypt psql password in unix script

[Vick Khera]

On Wed, Jul 8, 2015 at 2:46 PM, John R Pierce  wrote:

> but what security does that gain you?if someone gets your
> encrypted/hashed password, he can still log on.   the pgpass file has to be
> permissions 700, so only YOU (and root) can read it.
>

Exactly this. If you want a script to authenticate to postgres (or anything
else) then somewhere you need something to be in the clear, whether it be
the key to decrypt the password or a private key. If you can't trust the
local file system and users, then you can't do what you want.

Re: [GENERAL] [SQL] encrypt psql password in unix script

[Xavier Stevens]

I use envcrypt for things like this locally. Just encrypt the file with
your own PGP key.

https://github.com/whilp/envcrypt

On Wed, Jul 8, 2015 at 12:01 PM, Steve Midgley  wrote:

> My suggestion is to put it in an environment variable and set that
> variable from a shell startup script that is secured with permissions. (
> http://www.postgresql.org/docs/9.4/static/libpq-envars.html)
>
> If you can't do that, the only other method I've used is to setup Postgres
> with Ansible, and store the Pg passwords in an ansible vault, which is
> encrypted. Ansible asks for the decrypt key when it runs.
>
> Steve
>
>
> On Wed, Jul 8, 2015 at 11:34 AM, Suresh Raja 
> wrote:
>
>> Hi:
>>
>> I cannot use .pgpass as the password stored here is not encrypted.
>>
>> can i use a encrypted password from unix shell script.  has anybody ran
>> into same situation. Wht options do i have.
>>
>> Thanks,
>> -SR
>>
>
>

[GENERAL] Oracle to PostgreSQL Migration - Need Information

[Tim Clotworthy]

Hello,

I have a customer that is about to undertake a migration of an Oracle 11g
database to PostgreSQL 9.x (exact version to be determined). I am talking
not only of the migration of schemas and data, but also of a substantial
codebase of Pl/SQL stored procedures, as well as many triggers.

I don't think they know yet what they are up against. Everything I have
read is that this is a very substantial effort. At this stage, they would
be particularly interested in realistic and practical information on how to
estimate the effort required as well as any best-practices or guidance on
transition strategies.

I have found official documentation on the PostgreSQL site for porting
Pl/SQL to PL/pgSQL. This is excellent technical documentation. However,
there success will require that they are well prepared realistically
understanding the scope of the effor they are asbout to undertake.

Thanks for any response!

Re: [GENERAL] [SQL] encrypt psql password in unix script

[John R Pierce]

On 7/8/2015 12:01 PM, Steve Midgley wrote:
My suggestion is to put it in an environment variable and set that 
variable from a shell startup script that is secured with permissions. 
(http://www.postgresql.org/docs/9.4/static/libpq-envars.html)




that just moves the problem, now the plaintext password is in a script 
file somewhere, AND many OS's let other users see your environment.


If you can't do that, the only other method I've used is to setup 
Postgres with Ansible, and store the Pg passwords in an ansible vault, 
which is encrypted. Ansible asks for the decrypt key when it runs.




how would that work for unattended scripts, such as cron jobs ?



--
john r pierce, recycling bits in santa cruz



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

Re: [GENERAL] Oracle to PostgreSQL Migration - Need Information

[Tim Clarke]

This is almost exactly what we did around 8 years ago; obviously the
version numbers have changed. The reason we chose Postgres was the
enormous similarity between the two languages plus the overwhelming ROI
on the migration; my CEO had a spontaneous nosebleed when the Oracle
licensing costs were revealed one year.

Migration requires some thought, investigation, a clear plan and testing
- but I'm probably preaching to the converted.

We did it, we've never looked back and have had at least 8 long very
happy, stable and productive years and are looking forward to many more
(raises a glass to the developers and maintainers).

Tim Clarke

On 08/07/15 20:24, Tim Clotworthy wrote:
> Hello, 
>  
> I have a customer that is about to undertake a migration of an Oracle 11g 
> database to PostgreSQL 9.x (exact version to be determined). I am talking 
> not only of the migration of schemas and data, but also of a substantial 
> codebase of Pl/SQL stored procedures, as well as many triggers. 
>  
> I don't think they know yet what they are up against. Everything I have 
> read is that this is a very substantial effort. At this stage, they would 
> be particularly interested in realistic and practical information on
> how to 
> estimate the effort required as well as any best-practices or guidance on 
> transition strategies. 
>  
> I have found official documentation on the PostgreSQL site for porting 
> Pl/SQL to PL/pgSQL. This is excellent technical documentation. However, 
> there success will require that they are well prepared realistically 
> understanding the scope of the effor they are asbout to undertake. 
>  
> Thanks for any response! 

Re: [GENERAL] Oracle to PostgreSQL Migration - Need Information

[John McKown]

On Wed, Jul 8, 2015 at 2:24 PM, Tim Clotworthy <
tclotwor...@bluestonelogic.com> wrote:

> Hello,
>
> I have a customer that is about to undertake a migration of an Oracle 11g
> database to PostgreSQL 9.x (exact version to be determined). I am talking
> not only of the migration of schemas and data, but also of a substantial
> codebase of Pl/SQL stored procedures, as well as many triggers.
>
> I don't think they know yet what they are up against. Everything I have
> read is that this is a very substantial effort. At this stage, they would
> be particularly interested in realistic and practical information on how
> to
> estimate the effort required as well as any best-practices or guidance on
> transition strategies.
>
> I have found official documentation on the PostgreSQL site for porting
> Pl/SQL to PL/pgSQL. This is excellent technical documentation. However,
> there success will require that they are well prepared realistically
> understanding the scope of the effor they are asbout to undertake.
>
> Thanks for any response!
>

​Why are they converting?

Would EnterpriseDB (a commercial version of PostgreSQL which has extensions
to make it a "drop in" replacement for Oracle) be a possibility?
http://www.enterprisedb.com/solutions/oracle-compatibility-technology
​


-- 

Schrodinger's backup: The condition of any backup is unknown until a
restore is attempted.

Yoda of Borg, we are. Futile, resistance is, yes. Assimilated, you will be.

He's about as useful as a wax frying pan.

10 to the 12th power microphones = 1 Megaphone

Maranatha! <><
John McKown

Re: [GENERAL] Oracle to PostgreSQL Migration - Need Information

[dinesh kumar]

Hi,

We recently done the similar migration for one of our customer. We used all
opensource tools to achieve this migration process.

We used Pentaho Data Integration tool for doing Online DB migration, which
took minimal downtime with CDC{Change Data Capture} approach. Also, we used
Ora2Pg tool to migrate the DB objects with some manual syntax modifications.

Regards,
Dinesh
manojadinesh.blogspot.com

On Wed, Jul 8, 2015 at 12:24 PM, Tim Clotworthy <
tclotwor...@bluestonelogic.com> wrote:

> Hello,
>
> I have a customer that is about to undertake a migration of an Oracle 11g
> database to PostgreSQL 9.x (exact version to be determined). I am talking
> not only of the migration of schemas and data, but also of a substantial
> codebase of Pl/SQL stored procedures, as well as many triggers.
>
> I don't think they know yet what they are up against. Everything I have
> read is that this is a very substantial effort. At this stage, they would
> be particularly interested in realistic and practical information on how
> to
> estimate the effort required as well as any best-practices or guidance on
> transition strategies.
>
> I have found official documentation on the PostgreSQL site for porting
> Pl/SQL to PL/pgSQL. This is excellent technical documentation. However,
> there success will require that they are well prepared realistically
> understanding the scope of the effor they are asbout to undertake.
>
> Thanks for any response!
>

Re: [GENERAL] Oracle to PostgreSQL Migration - Need Information

[John R Pierce]

On 7/8/2015 1:16 PM, dinesh kumar wrote:
We recently done the similar migration for one of our customer. We 
used all opensource tools to achieve this migration process.


We used Pentaho Data Integration tool for doing Online DB migration, 
which took minimal downtime with CDC{Change Data Capture} approach. 
Also, we used Ora2Pg tool to migrate the DB objects with some manual 
syntax modifications.




thats the easy part.

now what about the massive code base of pl/sql and triggers he mentioned ?



--
john r pierce, recycling bits in santa cruz



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

Re: [GENERAL] Oracle to PostgreSQL Migration - Need Information

[CS DBA]

On 07/08/2015 02:20 PM, John R Pierce wrote:
> On 7/8/2015 1:16 PM, dinesh kumar wrote:
>> We recently done the similar migration for one of our customer. We
>> used all opensource tools to achieve this migration process.
>>
>> We used Pentaho Data Integration tool for doing Online DB migration,
>> which took minimal downtime with CDC{Change Data Capture} approach.
>> Also, we used Ora2Pg tool to migrate the DB objects with some manual
>> syntax modifications.
>>
>
> thats the easy part.
>
> now what about the massive code base of pl/sql and triggers he
> mentioned ?
>
>
>
Have you considered using ora2pg?
http://ora2pg.darold.net/

We've done several client migrations with it, quite successfully



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

Re: [GENERAL] Oracle to PostgreSQL Migration - Need Information

[dinesh kumar]

On Wed, Jul 8, 2015 at 1:20 PM, John R Pierce  wrote:

> On 7/8/2015 1:16 PM, dinesh kumar wrote:
>
>> We recently done the similar migration for one of our customer. We used
>> all opensource tools to achieve this migration process.
>>
>> We used Pentaho Data Integration tool for doing Online DB migration,
>> which took minimal downtime with CDC{Change Data Capture} approach. Also,
>> we used Ora2Pg tool to migrate the DB objects with some manual syntax
>> modifications.
>>
>>
> thats the easy part.
>
> now what about the massive code base of pl/sql and triggers he mentioned ?
>
>
Yeah, we need to rewrite the business logic if there are any un-supported
features like autonomous transactions, packages, nested procedures, e.t.c.

Regards,
Dinesh
manojadinesh.blogspot.com


>
> --
> john r pierce, recycling bits in santa cruz
>
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

Re: [GENERAL] Oracle to PostgreSQL Migration - Need Information

[Joshua D. Drake]

On 07/08/2015 12:47 PM, John McKown wrote:



​Why are they converting?

Would EnterpriseDB (a commercial version of PostgreSQL which has
extensions to make it a "drop in" replacement for Oracle) be a possibility?
http://www.enterprisedb.com/solutions/oracle-compatibility-technology


Because EDB is expensive. Why go from one closed source solution to 
another when you can go to the best Open Source database and forgo all 
of that?


Yes, EDB has some nice tools (no denying that) but porting from Oracle 
to PostgreSQL proper is not difficult in the least (although time 
consuming).


Sincerely,

JD


--
Command Prompt, Inc. - http://www.commandprompt.com/  503-667-4564
PostgreSQL Centered full stack support, consulting and development.
Announcing "I'm offended" is basically telling the world you can't
control your own emotions, so everyone else should do it for you.


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

Re: [GENERAL] Oracle to PostgreSQL Migration - Need Information

[Melvin Davidson]

I would start by looking at how many databases, schemas, tables and views
are involved. Then look at how many individual Oracle functions need to be
converted to plpgsql. You also need to investigate if there are any custom
data types. I do not have the formula, but I am sure there is a general
time factor involved in converting x databases, y schemas and z tables. You
did not mention what hardware is involved, but I am also sure there is a
time factor involved in copying / converting data from Oracle to
PostgreSQL, and you need to consider how much data you need to move and the
order, as there are probably foreign keys involved also.

You might want to do a small test to see how long it takes to dump 10k rows
of data from 1 tOracle table and load to PostgreSQL. That will at least
give you a general idea of how long it will take to move all data.

On Wed, Jul 8, 2015 at 5:20 PM, Joshua D. Drake 
wrote:

>
> On 07/08/2015 12:47 PM, John McKown wrote:
>
>
>> ​Why are they converting?
>>
>> Would EnterpriseDB (a commercial version of PostgreSQL which has
>> extensions to make it a "drop in" replacement for Oracle) be a
>> possibility?
>> http://www.enterprisedb.com/solutions/oracle-compatibility-technology
>>
>
> Because EDB is expensive. Why go from one closed source solution to
> another when you can go to the best Open Source database and forgo all of
> that?
>
> Yes, EDB has some nice tools (no denying that) but porting from Oracle to
> PostgreSQL proper is not difficult in the least (although time consuming).
>
> Sincerely,
>
> JD
>
>
> --
> Command Prompt, Inc. - http://www.commandprompt.com/  503-667-4564
> PostgreSQL Centered full stack support, consulting and development.
> Announcing "I'm offended" is basically telling the world you can't
> control your own emotions, so everyone else should do it for you.
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>



-- 
*Melvin Davidson*
I reserve the right to fantasize.  Whether or not you
wish to share my fantasy is entirely up to you.

Re: [GENERAL] Backup Method

[Francisco Reyes]

On 07/03/2015 08:08 AM, howardn...@selestial.com wrote:

I am trying to move away from pg_dump as it is proving too slow.



Have you looked into barman?
http://www.pgbarman.org

Also, another potential approach is to setup replication and to do the 
backups from the slave.



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

[GENERAL] How to test SSL cert from CA?

[Francisco Reyes]

Have a client using a commercial application. For a year plus we had 
been using a local self signed certificate without issues. As of a few 
weeks ago a change/update to the program is making it complain about the 
self signed cert.


I bought a SSL cert and installed it, but the program is still having 
the issue.


Anyone knows of a way to test the SSL connection such that it validates 
against the CA? Preferably an open source application. Connecting 
through psql works fine on SSL with what I have setup, but the 
application, xtuple, seems to still be having the issue.


The client already wrote to the application support department, but 
still waiting for an answer from them.


If I had a way to at least reproduce the error I could more easily track 
down what I am missing.


Any suggestions?


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

Re: [GENERAL] How to test SSL cert from CA?

[Tom Lane]

Francisco Reyes  writes:
> Have a client using a commercial application. For a year plus we had 
> been using a local self signed certificate without issues. As of a few 
> weeks ago a change/update to the program is making it complain about the 
> self signed cert.

What's the complaint exactly?

A whole lot of stuff has been broken lately by recent changes in OpenSSL
that make it reject certs with smaller key sizes.  You might need to
re-generate your cert with a larger size.

regards, tom lane


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

Re: [GENERAL] Oracle to PostgreSQL Migration - Need Information

[William Dunn]

On Wed, Jul 8, 2015 at 5:20 PM, Joshua D. Drake 
 wrote:

>
> On 07/08/2015 12:47 PM, John McKown wrote:
>
>
>> ​Why are they converting?
>>
>> Would EnterpriseDB (a commercial version of PostgreSQL which has
>> extensions to make it a "drop in" replacement for Oracle) be a
>> possibility?
>> http://www.enterprisedb.com/solutions/oracle-compatibility-technology
>>
>
> Because EDB is expensive. Why go from one closed source solution to
> another when you can go to the best Open Source database and forgo all of
> that?
>
> Yes, EDB has some nice tools (no denying that) but porting from Oracle to
> PostgreSQL proper is not difficult in the least (although time consuming).


I would suggest refrain from dismissing EnterpriseDB's PostgreSQL Advanced
Server like that. It is not free like the community version of Postgres but
the cost pays for developer time spent adding the additional features which
make it capable of being a drop-in replacement of Oracle. For an
organization migrating off of Oracle paying for that would make a lot of
sense because of the developer time (and cost) saved by porting to that
rather than the additional effort of migration to community Postgres. It
also has the additional benefit of providing all the programming features
that their app developers have become used to when working with Oracle.

The EnterpriseDB developers are some of the most active contributors to the
community version of Postgres and the advancements made in the community
version are included in EnterpriseDB Advanced Server as well.

*Will J. Dunn*
*willjdunn.com *

On Wed, Jul 8, 2015 at 5:20 PM, Joshua D. Drake 
wrote:

>
> On 07/08/2015 12:47 PM, John McKown wrote:
>
>
>> ​Why are they converting?
>>
>> Would EnterpriseDB (a commercial version of PostgreSQL which has
>> extensions to make it a "drop in" replacement for Oracle) be a
>> possibility?
>> http://www.enterprisedb.com/solutions/oracle-compatibility-technology
>>
>
> Because EDB is expensive. Why go from one closed source solution to
> another when you can go to the best Open Source database and forgo all of
> that?
>
> Yes, EDB has some nice tools (no denying that) but porting from Oracle to
> PostgreSQL proper is not difficult in the least (although time consuming).
>
> Sincerely,
>
> JD
>
>
> --
> Command Prompt, Inc. - http://www.commandprompt.com/  503-667-4564
> PostgreSQL Centered full stack support, consulting and development.
> Announcing "I'm offended" is basically telling the world you can't
> control your own emotions, so everyone else should do it for you.
>
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

[GENERAL] Socket Connection Authentication

[basti]

Hello,
I try to use PHP with Postgresq1 socket connection. All works fine with
following entry in

pg_hba.conf

local  database  user trust 
or
local  database  user peer map=someuser


pg_ident.conf

# MAPNAME   SYSTEM-USERNAME PG-USERNAME
someuserwww-datadb-user

When I try

local  database  user md5

I get the error: "Peer authentication failed for user ..."
Is it possible to authenticate a user with md5 via socket connection?





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

Re: [GENERAL] Socket Connection Authentication

[John R Pierce]

On 7/8/2015 11:32 PM, basti wrote:

When I try

local  database  user md5

I get the error: "Peer authentication failed for user ..."
Is it possible to authenticate a user with md5 via socket connection?


yes, it is, I do it all the time.

 was this the first local line, or at least in front of any local all 
all  lines ?


were you using localhost to connect or leaving the host empty? localhost 
uses a host   entry.


--
john r pierce, recycling bits in santa cruz



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

Re: [GENERAL] Socket Connection Authentication

[Naveed Shaikh]

The peer authentication method works by obtaining the client's operating
system user name from the kernel and using it as the allowed database user name
(with optional user name mapping). This method is only supported on local
connections.

*Please check the below steps as an Example:*

*1>>* Create user edb_admin.
useradd edb_admin
*2>>* Create database user edb_admin or if already exist then make sure
that the password of database user and os level user must match.
createuser -sP edb_admin
*3>>* If os level user is not exist with same password then it will show
error like as follows:
[root@localhost pg_log]# /opt/PostgreSQL/9.3/bin/psql -U edb_admin -d edb
-p 5444
psql.bin: FATAL: Peer authentication failed for user edb_admin"
[root@localhost pg_log]#

Once you will create user with password then login to the os level
user nocp_admin
and try to connect by same command:
[edb_admin@localhost pg_log]$ /opt/PostgreSQL/9.3/bin/psql -U edb_admin -d
edb -p 5444
psql.bin (9.3.1.3)
Type "help" for help.
edb=> \q
[edb_admin@localhost pg_log]$

Hope this helps.


Thanks & Regards,
Naveed Shaikh




On Thu, Jul 9, 2015 at 12:02 PM, basti  wrote:

> Hello,
> I try to use PHP with Postgresq1 socket connection. All works fine with
> following entry in
>
> pg_hba.conf
>
> local  database  user trust
> or
> local  database  user peer map=someuser
>
>
> pg_ident.conf
>
> # MAPNAME   SYSTEM-USERNAME PG-USERNAME
> someuserwww-datadb-user
>
> When I try
>
> local  database  user md5
>
> I get the error: "Peer authentication failed for user ..."
> Is it possible to authenticate a user with md5 via socket connection?
>
>
>
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>