Fwd: Re: [GENERAL] Combine multiple text search configuration

[Johannes Graën]

On 2017-11-07 08:27, hmidi slim wrote:
> Hi, 
> Thank for your proposition but when to use this query : 
> (to_tsvector('english', document) || to_tsvector('french', document)) @@
> (to_tsquery('english', query) || to_tsquery('french', query))
> I think that the performance decrease and not a good solution for big
> amount of data. Is it?

You have more lexems when you combine two languages, but not twice as
many as there will be some overlap. That means your index will also be
be bigger than a single language index. Anyhow I would expect this
variant to perform better than querying two single columns
simultaneously. Maybe one of the FTS developers could comment on this?


-- 
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] Migrating money column from MS SQL Server to Postgres

[Allan Kamau]

On Thu, Nov 9, 2017 at 9:58 AM, Igal @ Lucee.org  wrote:

> On 11/8/2017 6:25 PM, Igal @ Lucee.org wrote:
>
>> On 11/8/2017 5:27 PM, Allan Kamau wrote:
>>
>>> Maybe using NUMERIC without explicitly stating the precision is
>>> recommended. This would allow for values with many decimal places to be
>>> accepted without truncation. Your field may need to capture very small
>>> values such as those in bitcoin trading or some banking fee or interest.
>>>
>>
>> That's a very good idea.  For some reason I thought that I tried that
>> earlier and it didn't work as expected, but I just tested it (again?) and
>> it seems to work well, so that's what I'll do.
>>
>
> Another weird thing that I noticed:
>
> On another column, "total_charged", that was migrated properly as a
> `money` type, when I run `sum(total_charged::money)` I get `null`, but if I
> cast it to numeric, i.e. `sum(total_charged::numeric)`, I get the expected
> sum result.
>
> Is there a logical explanation to that?
>
>
> Igal
>
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

Since you are migrating data into a staging table in PostgreSQL, you may
set the field data type as TEXT for each field where you have noticed or
anticipate issues.
Then after population perform the datatype transformation query on the
given fields to determine the actual field value that could not be
gracefully transformed.
For example
SELECT a.* FROM . a WHERE
a.::NUMERIC IS NULL LIMIT 10;


or to identify values not within the expected range, substitute the place
holders in the query below with appropriate values and issue the query.

SELECT a.* FROM . a WHERE NOT
a.::NUMERIC BETWEEN
 AND  LIMIT 10;


Once you have determined the issues and solved them. Construct a second
table having similar field names but more restrictive (correct) data types
such as NUMERIC where appropriate. The insert into this table the data from
the staging table. Your insertion query would have the data casting clauses.


Allan.

Re: [GENERAL] Combine multiple text search configuration

[Aleksandr Parfenov]

On Thu, 9 Nov 2017 09:11:07 +0100
Johannes Graën  wrote:

> On 2017-11-07 08:27, hmidi slim wrote:
> > Hi, 
> > Thank for your proposition but when to use this query : 
> > (to_tsvector('english', document) || to_tsvector('french',
> > document)) @@ (to_tsquery('english', query) || to_tsquery('french',
> > query)) I think that the performance decrease and not a good
> > solution for big amount of data. Is it?  
> 
> You have more lexems when you combine two languages, but not twice as
> many as there will be some overlap. That means your index will also be
> be bigger than a single language index. Anyhow I would expect this
> variant to perform better than querying two single columns
> simultaneously. Maybe one of the FTS developers could comment on this?

Hi,

You are right in assumption about index size. However, difference
between a shared index and two single indices depends on dictionaries,
because some them doesn't return lexemes for unknown words.

Unfortunately, there is no alternative way in PostgreSQL 10 or earlier
to do multilingual text processing.

I'm working on a patch for flexible full-text search configuration and
one of the problems I'm want to solve is multilingual search without
separate indices for each language. The patch allows combining output
of more than one dictionary using UNION operator.

Current version of the patch is a demonstration of new features and
syntax for FTS configuration. The syntax itself is still at the
discussion stage. You can check it out at pgsql-hackers mailing list if
you are interested in[1]. Any feedback on the patch in terms of
internals, syntax, behavior or idea is welcome.

[1]
https://www.postgresql.org/message-id/flat/20171019172409.731f52a7@asp437-24-g082ur/

-- 
Aleksandr Parfenov
Postgres Professional: http://www.postgrespro.com
Russian Postgres Company


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

[GENERAL] Oracle to PostgreSQL

[Brahmam Eswar]

Hi ,

How to migrate the Oracle collection types ( IsTableOF, IsRecord) to
postgres.

-- 
Thanks & Regards,
Brahmeswara Rao J.

Re: [GENERAL] Oracle to PostgreSQL

[Laurenz Albe]

Brahmam Eswar wrote:
> How to migrate the Oracle collection types ( IsTableOF, IsRecord) to postgres.

Are you talking about table definitions or PL/SQL code?

Yours,
Laurenz Albe


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

[GENERAL] Postgresql and github

[Poul Kristensen]

Hi!

How come that Postgresql is not present in a github with latest release?

It would be very convenient to deploy PG using Ansible.

Oracle(latest release) is available through github.

TIA

Poul

Re: [GENERAL] Oracle to PostgreSQL

[Brahmam Eswar]

Here is the snippet of it.

*Oracle :*

*Declaration part in Store Procedure*

 CREATE OR REPLACE PROCEDURE "A"."Datastore"
 (

 , In_Param1 IN VARCHAR2
 , In_Param2 IN VARCHAR2
 , In_Param3 IN VARCHAR2
 , Out_Param1 OUT VARCHAR2
 , ERROR_MSG OUT VARCHAR2
 ) AS
   TEMP_ERR_MSG VARCHAR2(4000);
   IS_FIRST CHAR(1);
   TYPE INV_LINES_RT *IS RECORD*(
 VENDOR_NUM A.Datastore.VENDOR_NUM%TYPE,
 VENDOR_SITE_CODE A.Datastore.VENDOR_SITE_CODE%TYPE,
 INVOICE_NUM A.Datastore.INVOICE_NUM%TYPE,
 TXN_CNT NUMBER
   );
   TYPE INV_LINES_T *IS TABLE OF* INV_LINES_RT;
   L_INV_LINES INV_LINES_T;


*Looping Part :*

 IS_FIRST:='Y';
 WITH Distinct_Invoices As ( select DISTINCT
VENDOR_NUM,VENDOR_SITE_CODE,INVOICE_NUM,DESCRIPTION,CURR_CODE,INVOICE_AMOUNT,IMAGE_NUMBER,
VENDOR_NAME, PAYMENT_METHOD, GL_DATE, TERMS_NAME, RETURN_ID, PAYGROUP,
INVOICE_TYPE, INVOICE_DATE from A.Datastore where CASE_ID = In_Param1 )
 Select VENDOR_NUM,VENDOR_SITE_CODE,INVOICE_NUM, count(*) as TXN_CNT * BULK
COLLECT INTO* L_INV_LINES from Distinct_Invoices group by
VENDOR_NUM,VENDOR_SITE_CODE,INVOICE_NUM having count(*) > 1;
   IF  L_INV_LINES.COUNT>0 THEN

 ERROR_COUNT:=0;
   FOR indx in L_INV_LINES.first .. L_INV_LINES.last LOOP
 IF IS_FIRST!='Y' THEN
  TEMP_ERR_MSG  :=  TEMP_ERR_MSG || ', ';
 END IF;
 IS_FIRST:='N';
 TEMP_ERR_MSG  :=  TEMP_ERR_MSG ||  ||
L_INV_LINES(indx).INVOICE_NUM || ;


How do i convert this into PostgreSQL.

On Thu, Nov 9, 2017 at 4:48 PM, Laurenz Albe 
wrote:

> Brahmam Eswar wrote:
> > How to migrate the Oracle collection types ( IsTableOF, IsRecord) to
> postgres.
>
> Are you talking about table definitions or PL/SQL code?
>
> Yours,
> Laurenz Albe
>



-- 
Thanks & Regards,
Brahmeswara Rao J.

Re: [GENERAL] Postgresql and github

[Michael Paquier]

On Thu, Nov 9, 2017 at 8:27 PM, Poul Kristensen  wrote:
> How come that Postgresql is not present in a github with latest release?
>
> It would be very convenient to deploy PG using Ansible.
>
> Oracle(latest release) is available through github.

You are looking for that, which is a mirror of the official repository
in git.postgresql.org:
https://github.com/postgres/postgres
There is as well a section with release tarballs (so do the facilities
offered by community by the way).
-- 
Michael


-- 
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

[Laurenz Albe]

On Thu, 2017-11-09 at 17:01 +0530, Brahmam Eswar wrote:
> Here is the snippet of it.

>TYPE INV_LINES_RT IS RECORD(
>  VENDOR_NUM A.Datastore.VENDOR_NUM%TYPE,
>  VENDOR_SITE_CODE A.Datastore.VENDOR_SITE_CODE%TYPE,
>  INVOICE_NUM A.Datastore.INVOICE_NUM%TYPE,
>  TXN_CNT NUMBER
>);

You can create a composite type in PostgreSQL:

   CREATE TYPE complex AS (r integer, i integer);

>TYPE INV_LINES_T IS TABLE OF INV_LINES_RT;

You would use an array in this case:

   DECLARE
  carr complex[];

Yours,
Laurenz Albe


-- 
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

[Felix Kunde]

On Thu, 2017-11-09 at 17:01 +0530, Brahmam Eswar wrote:
> You can create a composite type in PostgreSQL:
> CREATE TYPE complex AS (r integer, i integer);
> You would use an array in this case:
> DECLARE
> carr complex[];

I've once faced the same thing, and did as Laurenz suggested.
You will like Postgres' array support.


-- 
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] Migrating money column from MS SQL Server to Postgres

[Adam Brusselback]

> Since you are migrating data into a staging table in PostgreSQL, you may set
> the field data type as TEXT for each field where you have noticed or
> anticipate issues.
> Then after population perform the datatype transformation query on the given
> fields to determine the actual field value that could not be gracefully
> transformed.

This is the approach I have come to as the most successful for data migrations.

I will use tools like Kettle / Talend to get data into a staging table
with every column as text, then use SQL to migrate that to a properly
typed table.  Works much better than trying to work within the
constraints of these tools.


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

[GENERAL] Migrating plattaform

[Valdir Kageyama]

Hello,

I need migrated the postgres from Linux on IBM Power to Oracle Linux on
SPARC.

My doubt is possible copy the datafiles to new enviorement ? or I need
using  other means of copying the data.
For exemples: pg_dump/pg_restore.


regards

Tikara

Re: [GENERAL] Postgresql and github

[Igal @ Lucee.org]

On 11/9/2017 3:27 AM, Poul Kristensen wrote:

How come that Postgresql is not present in a github with latest release?


What do you mean?  Is this not what you're referring to:
    https://github.com/postgres/postgres/releases
?

Igal Sapir
Lucee Core Developer
Lucee.org 

Re: [GENERAL] Migrating money column from MS SQL Server to Postgres

[Merlin Moncure]

On Thu, Nov 9, 2017 at 8:22 AM, Adam Brusselback
 wrote:
>> Since you are migrating data into a staging table in PostgreSQL, you may set
>> the field data type as TEXT for each field where you have noticed or
>> anticipate issues.
>> Then after population perform the datatype transformation query on the given
>> fields to determine the actual field value that could not be gracefully
>> transformed.
>
> This is the approach I have come to as the most successful for data 
> migrations.
>
> I will use tools like Kettle / Talend to get data into a staging table
> with every column as text, then use SQL to migrate that to a properly
> typed table.  Works much better than trying to work within the
> constraints of these tools.

YES

I call the approach 'ELT', (Extract, Load, Trasform).  You are much
better off writing transformations in SQL than inside of an ETL tool.
This is a perfect example of why.

merlin


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

[GENERAL] Index not used when using expression

[Dingyuan Wang]

Hi,

I have a table named "gps", with an indexed column "packettime", which
has unix timestamps.

The following query:

select * from gps where packettime < extract(epoch from '2017-05-01
08:00+08'::timestamp with time zone)

explains to:

Seq Scan on gps  (cost=0.00..43411860.64 rows=384325803 width=120)
  Filter: ((packettime)::double precision < date_part('epoch'::text,
'2017-05-01 08:00:00+08'::timestamp with time zone))

While this query:

select * from gps where packettime < 1493596800

explains to:

Index Scan using idx_gps_packettime on gps  (cost=0.58..2661058.92
rows=8912880 width=120)
  Index Cond: (packettime < 1493596800)

The above behaviour is the same whether the query is long or short.
PostgreSQL doesn't seem like to pre-compute the function value.

So how can I make it pre-compute the timestamp and use the index, or I
have to manually calculate the timestamp? Is this an intended behaviour?


-- 
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] Index not used when using expression

[Tom Lane]

Dingyuan Wang  writes:
> I have a table named "gps", with an indexed column "packettime", which
> has unix timestamps.

> The following query:

> select * from gps where packettime < extract(epoch from '2017-05-01
> 08:00+08'::timestamp with time zone)

> explains to:

> Seq Scan on gps  (cost=0.00..43411860.64 rows=384325803 width=120)
>   Filter: ((packettime)::double precision < date_part('epoch'::text,
> '2017-05-01 08:00:00+08'::timestamp with time zone))

The reason that's not working for you is that the query is not testing
packettime, it's testing packettime::float8, because date_part() returns
float8.  You could cast the result of date_part() to bigint, or whatever
type the packettime column actually is, so that the comparison is to
the unadorned variable.

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] Migrating plattaform

[Jaime Soler]

You should use pg_dump & pg_restore because datafiles of differents on
architectures are incompatibles.


El 9 nov. 2017 16:47, "Valdir Kageyama"  escribió:

> Hello,
>
> I need migrated the postgres from Linux on IBM Power to Oracle Linux on
> SPARC.
>
> My doubt is possible copy the datafiles to new enviorement ? or I need
> using  other means of copying the data.
> For exemples: pg_dump/pg_restore.
>
>
> regards
>
> Tikara
>

Re: [GENERAL] Postgresql and github

[Poul Kristensen]

No it isn't.

What I want to do is:

ansible-playbook   somepostgresql.yml

and postgresql is then changed on some server
the way things are done by e.g.
github.com/oravirt/ansible-oracle

Thanks
Poul



2017-11-09 17:18 GMT+01:00 Igal @ Lucee.org :

> On 11/9/2017 3:27 AM, Poul Kristensen wrote:
>
> How come that Postgresql is not present in a github with latest release?
>
>
> What do you mean?  Is this not what you're referring to:
> https://github.com/postgres/postgres/releases
> ?
>
> Igal Sapir
> Lucee Core Developer
> Lucee.org 
>



-- 
Med venlig hilsen / Best regards
Poul Kristensen
Linux-OS/Virtualizationexpert and Oracle DBA

Re: [GENERAL] Postgresql and github

[Nicklas Avén]

On Thu, 2017-11-09 at 12:27 +0100, Poul Kristensen wrote:
> Hi!
> 
> How come that Postgresql is not present in a github with latest
> release?
> 
> It would be very convenient to deploy PG using Ansible.
> 
> Oracle(latest release) is available through github.
> 
> TIA
> 
> Poul
> 

How can you say Oracle is present on github?
In the link in your last post it clearly states that "You'll need to
manually download the Oracle software and make it available to the
control-machine (either locally or on a web-server, or through a nfs-
share) before running the playbook."


As I understand it ansible is just some deployment automation?


PostgreSQL is available as binaries and sources from a lot of places,
but Oracle is not, or am I missing something here?



Regards


Nicklas Avén

Re: [GENERAL] Postgresql and github

[Steve Atkins]

> On Nov 9, 2017, at 9:37 AM, Poul Kristensen  wrote:
> 
> No it isn't.
> 
> What I want to do is:
> 
> ansible-playbook   somepostgresql.yml 
> 
> and postgresql is then changed on some server
> the way things are done by e.g.
> github.com/oravirt/ansible-oracle 

You're looking for help with an Ansible recipe, not with anything to do with 
PostgreSQL itself.

Mentioning it here, in case someone already has one, is worth a try but you're 
likely going to need to go talk to the Ansible people. Or write your own.

Cheers,
  Steve



-- 
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] Postgresql and github

[Poul Kristensen]

Dear friends,

The reason I posted this:

For the last 6 months I have noticed the enormous numbers of github's
raising on the Internet.
For the last 10 years I have worked with Oracle(yes expensive)and
Postgresql(from version 8.4!) and I am familiar conserning the _big_
difference between the to database environments. Good and not so good.
Administration well Oracle is easier because of
the instance/backup/restore(point in time) crontrolled by itselr.
Postgresql does not have that kind of stuff - I mean backup/restore(pointin
time
controlled by _itself_. But it is free and free of charge. But is not 24/7
when upgrading.
Therefore:
To make administration easier for your customers I suggest to establish a
github with different possibilites, showing how to upgrade minor/major
in 24/7 enviroments especially the restore(point in time) as a "build in"
method liek Oracle'srman. I have been told by a consultant that
pgBackRest is recommended. But it is not buildin in the way Oracle's rman.
Sorry if i got out of the scope of my mail. And sorry for my demands.:)
BTW: why are PG databases visible by all users (prompt: \l)


Thanks
Poul







2017-11-09 19:00 GMT+01:00 Steve Atkins :

>
> > On Nov 9, 2017, at 9:37 AM, Poul Kristensen  wrote:
> >
> > No it isn't.
> >
> > What I want to do is:
> >
> > ansible-playbook   somepostgresql.yml
> >
> > and postgresql is then changed on some server
> > the way things are done by e.g.
> > github.com/oravirt/ansible-oracle
>
> You're looking for help with an Ansible recipe, not with anything to do
> with PostgreSQL itself.
>
> Mentioning it here, in case someone already has one, is worth a try but
> you're likely going to need to go talk to the Ansible people. Or write your
> own.
>
> Cheers,
>   Steve
>
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>



-- 
Med venlig hilsen / Best regards
Poul Kristensen
Linux-OS/Virtualizationexpert and Oracle DBA

[GENERAL] OpeSSL - PostgreSQL

[chiru r]

Hi All,

I am using PostgreSQL version *9.5.7* on Red hat enterprise Linux *7.2.*

*OpenSSL version : * OpenSSL 1.0.1e-fips 11 Feb 2013.

I have a requirement to enable the SSL in my environment with specific
cipher suites,we want to restrict weak cipher suites from open SSL default
list.

We have list of cipher suites, which are authorized to use in my
environment.So the Client Applications use one of authorized cipher suites
while configuring application server.

Is it require to install different version of OpenSSL software instead of
default OpenSSL on Linux ?.

How to configure the PostgreSQL to allow specif cipher suites from
different client applications?


Thanks,
Chiru

Re: [GENERAL] OpeSSL - PostgreSQL

[John R Pierce]

On 11/9/2017 1:59 PM, chiru r wrote:


How to configure the PostgreSQL to allow specif cipher suites from 
different client applications?


see https://www.postgresql.org/docs/9.5/static/ssl-tcp.html


--
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] OpeSSL - PostgreSQL

[Tom Lane]

John R Pierce  writes:
> On 11/9/2017 1:59 PM, chiru r wrote:
>> How to configure the PostgreSQL to allow specif cipher suites from 
>> different client applications?

> see https://www.postgresql.org/docs/9.5/static/ssl-tcp.html

Note that while you can adjust ssl_ciphers, it's a postmaster-wide
setting; there is not a provision for letting it be set per-user.
Since the SSL handshake necessarily occurs before we find out which
user is trying to connect, it'd be hard to do differently.

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] [HACKERS] OpeSSL - PostgreSQL

[Joe Conway]

On 11/09/2017 01:59 PM, chiru r wrote:
> I am using PostgreSQL version *9.5.7* on Red hat enterprise Linux *7.2.*
> 
> *OpenSSL version : * OpenSSL 1.0.1e-fips 11 Feb 2013.
> 
> I have a requirement to enable the SSL in my environment with specific
> cipher suites,we want to restrict weak cipher suites from open SSL
> default list.
> 
> We have list of cipher suites, which are authorized to use in my
> environment.So the Client Applications use one of authorized cipher
> suites while configuring application server. 
> 
> Is it require to install different version of OpenSSL software instead
> of default OpenSSL on Linux ?.

Note -- please don't cross post to hackers as it is off topic for that
list and cross posting is generally frowned upon (pgsql-hackers removed).

Assuming you mean that you need only FIPS 140-2 compliant ciphers, you
would want to configure the OS for system-wide FIPS compliance. See:

https://access.redhat.com/documentation/en-us/red_hat_enterprise_linux/7/html/security_guide/chap-federal_standards_and_regulations


> How to configure the PostgreSQL to allow specif cipher suites from
> different client applications?


If you still need more control over what Postgres allows, see the
ssl_ciphers configuration setting here:

https://www.postgresql.org/docs/current/static/runtime-config-connection.html#GUC-SSL

HTH,

Joe

-- 
Crunchy Data - http://crunchydata.com
PostgreSQL Support for Secure Enterprises
Consulting, Training, & Open Source Development



signature.asc
Description: OpenPGP digital signature

Re: [GENERAL] Postgresql and github

[Jeremy Schneider]

Hi Poul, and thanks for using PostgreSQL!  I've also been a very heavy
user of Oracle and now a heavy user of PostgreSQL.

I remember the days before Oracle acquired the RMAN software and
bundled it with their database.  Not so long ago, doing backups on
Oracle wasn't so different from PostgreSQL; either you did
storage-level snapshots or you used sql to begin/end backup.  At that
time there were many 3rd party tools for managing Oracle backups, much
like the situation with PostgreSQL today.  You can write your own
scripts for backup and recovery, or if you want a polished interface
then you can use any of the free or commercial PostgreSQL backup and
recovery tools on the market.

PostgreSQL certainly does have the advanced 24/7 backup and
point-in-time recovery capabilities that you should expect in any
serious database.  I would also include 2nd Quadrant's "pglogical"
replication extension which enables near zero-downtime major version
upgrades, similar to what you might do with GoldenGate on Oracle.

Regarding ansible, the Oracle playbook you referenced was published by
Mikael Sandström from Sweden. He and I have both worked on the RAC
Attack project and I remember talking with him about those Ansible
scripts when he first published them! He's not an Oracle employee but
just a community contributor. There are also lots of people
contributing PostgreSQL roles in Ansible Galaxy (backed by GitHub).  I
see over 300 Ansible roles; does the link below work for you?

https://galaxy.ansible.com/list#/roles?page=1&page_size=10&autocomplete=postgres

You're certainly right that we could use more step-by-step examples
for new users to PostgreSQL.  As you continue learning, I hope you'll
continue to share what stands out to you.  You might even consider
writing some blog posts yourself - I would love to read them and pass
them along to other new users!  And if you find any errors in the
official PostgreSQL documentation, by all means let us know and we
will address them.

Looking forward to hearing more from you!

-Jeremy


-- 
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] Migrating money column from MS SQL Server to Postgres

[Igal @ Lucee.org]

On 11/9/2017 8:19 AM, Merlin Moncure wrote:

On Thu, Nov 9, 2017 at 8:22 AM, Adam Brusselback
 wrote:

Since you are migrating data into a staging table in PostgreSQL, you may set
the field data type as TEXT for each field where you have noticed or
anticipate issues.
Then after population perform the datatype transformation query on the given
fields to determine the actual field value that could not be gracefully
transformed.

This is the approach I have come to as the most successful for data migrations.

I will use tools like Kettle / Talend to get data into a staging table
with every column as text, then use SQL to migrate that to a properly
typed table.  Works much better than trying to work within the
constraints of these tools.

YES

I call the approach 'ELT', (Extract, Load, Trasform).  You are much
better off writing transformations in SQL than inside of an ETL tool.
This is a perfect example of why.


All sound advice.  Thanks.


Igal Sapir

Lucee Core Developer
Lucee.org 

Re: [GENERAL] [HACKERS] OpeSSL - PostgreSQL

[chiru r]

Thanks.

If OpenSSL apply any patches at OS level, Is there any changes/maintenance
we need to perform at PostgreSQL end?

On Thu, Nov 9, 2017 at 5:46 PM, Joe Conway  wrote:

> On 11/09/2017 01:59 PM, chiru r wrote:
> > I am using PostgreSQL version *9.5.7* on Red hat enterprise Linux *7.2.*
> >
> > *OpenSSL version : * OpenSSL 1.0.1e-fips 11 Feb 2013.
> >
> > I have a requirement to enable the SSL in my environment with specific
> > cipher suites,we want to restrict weak cipher suites from open SSL
> > default list.
> >
> > We have list of cipher suites, which are authorized to use in my
> > environment.So the Client Applications use one of authorized cipher
> > suites while configuring application server.
> >
> > Is it require to install different version of OpenSSL software instead
> > of default OpenSSL on Linux ?.
>
> Note -- please don't cross post to hackers as it is off topic for that
> list and cross posting is generally frowned upon (pgsql-hackers removed).
>
> Assuming you mean that you need only FIPS 140-2 compliant ciphers, you
> would want to configure the OS for system-wide FIPS compliance. See:
>
> https://access.redhat.com/documentation/en-us/red_hat_
> enterprise_linux/7/html/security_guide/chap-federal_
> standards_and_regulations
>
>
> > How to configure the PostgreSQL to allow specif cipher suites from
> > different client applications?
>
>
> If you still need more control over what Postgres allows, see the
> ssl_ciphers configuration setting here:
>
> https://www.postgresql.org/docs/current/static/runtime-
> config-connection.html#GUC-SSL
>
> HTH,
>
> Joe
>
> --
> Crunchy Data - http://crunchydata.com
> PostgreSQL Support for Secure Enterprises
> Consulting, Training, & Open Source Development
>
>

Re: [GENERAL] [HACKERS] OpeSSL - PostgreSQL

[Joe Conway]

On 11/09/2017 05:52 PM, chiru r wrote:
> If OpenSSL apply any patches at OS level, Is there any
> changes/maintenance we need to perform at PostgreSQL end?
> 
> On Thu, Nov 9, 2017 at 5:46 PM, Joe Conway wrote:
>> Assuming you mean that you need only FIPS 140-2 compliant ciphers, you
>> would want to configure the OS for system-wide FIPS compliance. See:
>> 
>> https://access.redhat.com/documentation/en-us/red_hat_enterprise_linux/7/html/security_guide/chap-federal_standards_and_regulations

If you enable FIPS at the OS level on a RHEL 7.x system per that link,
Postgres will automatically be using SSL in fips-mode, nothing specific
you need to (or actually, even can) do.

Joe

-- 
Crunchy Data - http://crunchydata.com
PostgreSQL Support for Secure Enterprises
Consulting, Training, & Open Source Development



signature.asc
Description: OpenPGP digital signature