How to extract information from pg_ddl_command type

[Thomas Kellerer]

The function pg_event_trigger_ddl_commands() returns several columns, one of 
them being "command" that is of the type "pg_ddl_command". 

The manual[1] describes this column as: 

> A complete representation of the command, in internal format. 
> This cannot be output directly, but it can be passed to other 
> functions to obtain different pieces of information about the 
> command.

However, I can not find any of those "other functions" to extract information 
from that column. 

My goal is to get the complete SQL text that fired the event trigger - is that 
even possible through pg_event_trigger_ddl_commands()? 

Thanks
Thomas

[1] https://www.postgresql.org/docs/current/functions-event-triggers.html

Re: migrating from Oracle to PostgreSQL 11

[Hitesh Chadda]

Hello,
The target is PostgreSQL 10.1. I would like to know possible approach that
can be followed for doing the migration.

Regards
H.kumar



On Wednesday, June 26, 2019, Hitesh Chadda  wrote:

> Hi PostgresSQL Support,
>
> I have to migrate from Oracle 11g to PostgresSQL 11.4. Please suggest best
> solution for doing the migration.
>
> Regards
>
> H.Kumar
>

Re: migrating from Oracle to PostgreSQL 11

[Gavin Flower]

On 11/07/2019 17:53, Hitesh Chadda wrote:

Hello,
The target is PostgreSQL 10.1. I would like to know possible approach 
that can be followed for doing the migration.


Regards
H.kumar



On Wednesday, June 26, 2019, Hitesh Chadda > wrote:


Hi PostgresSQL Support,

I have to migrate from Oracle 11g to PostgresSQL 11.4. Please
suggest best solution for doing the migration.

Regards

H.Kumar


Hitesh,

Please don't top post, as the convention in these lists is to add new 
stuff at the bottom.


Why not upgrade Oracle to PostgresSQL 11?


Cheers,
Gavin

RE: How to run a task continuously in the background

[Steven Winfield]

pg_cron, perhaps?
https://www.citusdata.com/blog/2016/09/09/pgcron-run-periodic-jobs-in-postgres/

I _think_ it ticks all three of your boxes.


Steve.



This email is confidential. If you are not the intended recipient, please 
advise us immediately and delete this message. 
The registered name of Cantab- part of GAM Systematic is Cantab Capital 
Partners LLP. 
See - http://www.gam.com/en/Legal/Email+disclosures+EU for further information 
on confidentiality, the risks of non-secure electronic communication, and 
certain disclosures which we are required to make in accordance with applicable 
legislation and regulations. 
If you cannot access this link, please notify us by reply message and we will 
send the contents to you.

GAM Holding AG and its subsidiaries (Cantab – GAM Systematic) will collect and 
use information about you in the course of your interactions with us. 
Full details about the data types we collect and what we use this for and your 
related rights is set out in our online privacy policy at 
https://www.gam.com/en/legal/privacy-policy. 
Please familiarise yourself with this policy and check it from time to time for 
updates as it supplements this notice.

Re: migrating from Oracle to PostgreSQL 11

[Ryan Lambert]

On Thu, Jul 11, 2019 at 1:28 AM Hitesh Chadda 
wrote:

> Hello,
> The target is PostgreSQL 10.1. I would like to know possible approach that
> can be followed for doing the migration.
>
> Regards
> H.kumar
>
>
10.9 is the current release in Pg10 and contains multiple security fixes
over 10.1. There is no good reason to start a new project on an outdated
minor release with known security issues.
https://why-upgrade.depesz.com/show?from=10.1&to=10.9&keywords=

As Gavin asked, why not start on Pg11 with 11.4 being the current release?

Ryan Lambert

Extensions

[bhargav kamineni]

Hi Team,

I have a doubt regarding the extensions.My db has these many extensions
Name
---
 adminpack
 btree_gist
 dblink
 file_fdw
 fuzzystrmatch
 hstore
 insert_username
 intarray
 pageinspect
 pg_buffercache
 pg_freespacemap
 pg_repack
 pg_stat_statements
 pg_trgm
 pgcrypto
 plpgsql
 unaccent


How can i figure out what all extensions are being used ? is there any way ?

Thanks,
Bhargav

Re: Extensions

[Adrian Klaver]

On 7/11/19 6:54 AM, bhargav kamineni wrote:

Hi Team,

I have a doubt regarding the extensions.My db has these many extensions >       
   Name
---
  adminpack
  btree_gist
  dblink
  file_fdw
  fuzzystrmatch
  hstore
  insert_username
  intarray
  pageinspect
  pg_buffercache
  pg_freespacemap
  pg_repack
  pg_stat_statements
  pg_trgm
  pgcrypto
  plpgsql
  unaccent

I'm assuming the above came from using \dx in psql.




How can i figure out what all extensions are being used ? is there any way ?


One quick and dirty way I know is:

test=# begin ;
BEGIN
test=# drop extension plpgsql ;
ERROR:  cannot drop extension plpgsql because other objects depend on it
DETAIL:  function upgrade_serial_to_identity(regclass,name) depends on 
language plpgsql

function fizzbuzz(integer,integer) depends on language plpgsql
function child_fnc() depends on language plpgsql
function child_fnc(character varying) depends on language plpgsql
function parent_fnc() depends on language plpgsql

...

rollback;



Thanks,
Bhargav



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

Re: DRY up GUI wiki pages

[Tony Shelver]

> I created the first one because the second one was full of old, stale,
> useless things. I believe that everything valid on the second one was added
> to the first one at the time.
>
> Also look at https://wiki.postgresql.org/wiki/Design_Tools and
> https://wiki.postgresql.org/wiki/Documentation_Tools if you're looking to
> combine / divide things.
>
> Cheers,
>   Steve
>
>
While on the subect, pgmodeler has a change in website:
https://pgmodeler.io/

Re: How to run a task continuously in the background

[Michael Nolan]

A cron job will only run once a minute, not wake up every second.  But you
could write a PHP program that does a one-second sleep before checking if
there's something to do, and a batch job that runs periodically to see if
the PHP program is running, and if not, launch it.

That's how I handle a job that opens a tunnel from our PostgreSQL server to
a MySQL server running at AWS so we can synchronize data between those two
database servers.  It dies periodically for reasons we've never figured
out, so every 5 minutes I check to make sure it's running.
--
Mike Nolan
no...@tssi.com

On Thu, Jul 11, 2019 at 5:44 AM Steven Winfield <
steven.winfi...@cantabcapital.com> wrote:

> pg_cron, perhaps?
>
> https://www.citusdata.com/blog/2016/09/09/pgcron-run-periodic-jobs-in-postgres/
>
> I _think_ it ticks all three of your boxes.
>
>
> Steve.
>
>
>
> --
>
>
> *This email is confidential. If you are not the intended recipient, please
> advise us immediately and delete this message. The registered name of
> Cantab- part of GAM Systematic is Cantab Capital Partners LLP. See -
> http://www.gam.com/en/Legal/Email+disclosures+EU
>  for further information
> on confidentiality, the risks of non-secure electronic communication, and
> certain disclosures which we are required to make in accordance with
> applicable legislation and regulations. If you cannot access this link,
> please notify us by reply message and we will send the contents to you.GAM
> Holding AG and its subsidiaries (Cantab – GAM Systematic) will collect and
> use information about you in the course of your interactions with us. Full
> details about the data types we collect and what we use this for and your
> related rights is set out in our online privacy policy at
> https://www.gam.com/en/legal/privacy-policy
> . Please familiarise yourself
> with this policy and check it from time to time for updates as it
> supplements this notice-- *
>

Restore database into azure PostgreSQL

[mahesh ravilla]

Hi, when restore a on promises database into azure PostgreSQL. I am ge3a
error please help ASAP.

Could not connect to server :connection refused
Is the server running on host " azure_server_name" (000.000.000.00)  and
accepting
Tcp/ip connection on port 5432?

Thanks
Mahesh Ravilla

Re: Restore database into azure PostgreSQL

[Adrian Klaver]

On 7/11/19 9:25 AM, mahesh ravilla wrote:
Hi, when restore a on promises database into azure PostgreSQL. I am ge3a 
error please help ASAP.


Could not connect to server :connection refused
Is the server running on host " azure_server_name" (000.000.000.00)  and 
accepting

Tcp/ip connection on port 5432?


Connection troubleshooting:

1) Verify Postgres server is actually running.

2) Verify what port it is listening on(5432 is default).

3) Verify correct hostname.

4) Check to see if there is a firewall blocking the Postgres server port.



Thanks
Mahesh Ravilla




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

Re: Restore database into azure PostgreSQL

[Tim Clarke]

On 11/07/2019 17:29, Adrian Klaver wrote:
> On 7/11/19 9:25 AM, mahesh ravilla wrote:
>> Hi, when restore a on promises database into azure PostgreSQL. I am
>> ge3a error please help ASAP.
>>
>> Could not connect to server :connection refused
>> Is the server running on host " azure_server_name" (000.000.000.00)
>> and accepting
>> Tcp/ip connection on port 5432?
>
> Connection troubleshooting:
>
> 1) Verify Postgres server is actually running.
>
> 2) Verify what port it is listening on(5432 is default).
>
> 3) Verify correct hostname.
>
> 4) Check to see if there is a firewall blocking the Postgres server port.
>
>>
>> Thanks
>> Mahesh Ravilla
>>
>

Looks like that (dns?) symbol azure_server_name points to a broken IP
address?

Tim Clarke
IT Director
Direct: +44 (0)1376 504510 | Mobile: +44 (0)7887 563420



Telephone: Witham: +44(0)1376 503500 | London: +44 (0)20 3009 0853 | Frankfurt: 
+49 (0)69 7191 6000 | Hong Kong: +852 5803 1687 | Toronto: +1 647 503 2848
Web: https://www.manifest.co.uk/



Minerva Analytics Ltd - A Solactive Company
9 Freebournes Court | Newland Street | Witham | Essex | CM8 2BL | United Kingdom



Copyright: This e-mail may contain confidential or legally privileged 
information. If you are not the named addressee you must not use or disclose 
such information, instead please report it to 
ad...@minerva.info
Legal: Minerva Analytics is the trading name of: Minerva Analytics Ltd: 
Registered in England Number 11260966 & The Manifest Voting Agency Ltd: 
Registered in England Number 2920820 Registered Office at above address. Please 
Click Here https://www.manifest.co.uk/legal/ for further information.

Re: How to run a task continuously in the background

[Alan Hodgson]

On Thu, 2019-07-11 at 11:19 -0500, Michael Nolan wrote:
> A cron job will only run once a minute, not wake up every second. 
> But you could write a PHP program that does a one-second sleep before
> checking if there's something to do, and a batch job that runs
> periodically to see if the PHP program is running, and if not, launch
> it.  
> 
> That's how I handle a job that opens a tunnel from our PostgreSQL
> server to a MySQL server running at AWS so we can synchronize data
> between those two database servers.  It dies periodically for reasons
> we've never figured out, so every 5 minutes I check to make sure it's
> running. 

If you run such a job under systemd you can tell systemd to
automatically restart it if it dies.

Alternate, the application monit is an older means of monitoring and
restarting persistent processes, complete with email notifications.

This is quite a common system administration task. No need to roll your
own.

Postgresql and VBA - Connection Timeout

[Wayne Mell]

Can someone help me with executing a postgres function from VBA?  No matter 
what function I call, it always times out after exactly 30 seconds.  I've tried 
to use the statement_timeout a million different ways and always get an error 
that states "Cancelling statement due to statement timeout" in Access.

Here is the code I've been using to test:

Dim cnn As ADODB.Connection
Dim cnnCmd As ADODB.Command

 Set cnn = New ADODB.Connection
   cnn.ConnectionString = "Driver={PostgreSQL 
Unicode(x64)};Server=postgres.office.server;Port=5432;Database=DBS;UID=postgresuser;PWD=postgrespwd;"
   cnn.ConnectionTimeout = 0
   cnn.Open

Set cnnCmd = New ADODB.Command
cnnCmd.CommandTimeout = 0

Set cnnCmd = cnn.Execute("select pg_sleep(50);")

There are other functions that I've tried to execute, and they all work as long 
as they take less than 30 seconds to run.  I just started running pg_sleep to 
test.

If I log into pgAdmin with the same user name and password, and on the same 
machine, then the functions will execute no matter how long they take.  It's 
only through VBA and the psqlODBC driver that I have the 30 second timeout 
limit.

Has anyone found a way to make this work?  Thanks.

Re: Postgresql and VBA - Connection Timeout

[Adrian Klaver]

On 7/11/19 1:04 PM, Wayne Mell wrote:
Can someone help me with executing a postgres function from VBA?  No 
matter what function I call, it always times out after exactly 30 
seconds.  I've tried to use the statement_timeout a million different 
ways and always get an error that states "Cancelling statement due to 
statement timeout" in Access.


Here is the code I've been using to test:

Dim cnn As ADODB.Connection
Dim cnnCmd As ADODB.Command

  Set cnn = New ADODB.Connection
        cnn.ConnectionString = "Driver={PostgreSQL 
Unicode(x64)};Server=postgres.office.server;Port=5432;Database=DBS;UID=postgresuser;PWD=postgrespwd;"

        cnn.ConnectionTimeout = 0
    cnn.Open

Set cnnCmd = New ADODB.Command
cnnCmd.CommandTimeout = 0

Set cnnCmd = cnn.Execute("select pg_sleep(50);")

There are other functions that I've tried to execute, and they all work 
as long as they take less than 30 seconds to run.  I just started 
running pg_sleep to test.


If I log into pgAdmin with the same user name and password, and on the 
same machine, then the functions will execute no matter how long they 
take.  It's only through VBA and the psqlODBC driver that I have the 30 
second timeout limit.


Has anyone found a way to make this work?  Thanks.



https://www.postgresql-archive.org/BUG-odbc-statement-timeout-gets-set-to-3-td5992591.html







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

Re: Postgresql and VBA - Connection Timeout

[George Weaver]

On 11/07/2019 3:04 p.m., Wayne Mell wrote:
Can someone help me with executing a postgres function from VBA?  No 
matter what function I call, it always times out after exactly 30 
seconds.  I've tried to use the statement_timeout a million different 
ways and always get an error that states "Cancelling statement due to 
statement timeout" in Access.


Here is the code I've been using to test:

Dim cnn As ADODB.Connection
Dim cnnCmd As ADODB.Command

 Set cnn = New ADODB.Connection
       cnn.ConnectionString = "Driver={PostgreSQL 
Unicode(x64)};Server=postgres.office.server;Port=5432;Database=DBS;UID=postgresuser;PWD=postgrespwd;"

       cnn.ConnectionTimeout = 0
   cnn.Open

Set cnnCmd = New ADODB.Command
cnnCmd.CommandTimeout = 0



I found that the default of 0 meaning no timeout doesn't seem to work.


What happens if you set cnnCmd.CommandTimeout = 60 for the sleep test of 50?


George




Set cnnCmd = cnn.Execute("select pg_sleep(50);")

There are other functions that I've tried to execute, and they all 
work as long as they take less than 30 seconds to run.  I just started 
running pg_sleep to test.


If I log into pgAdmin with the same user name and password, and on the 
same machine, then the functions will execute no matter how long they 
take.  It's only through VBA and the psqlODBC driver that I have the 
30 second timeout limit.


Has anyone found a way to make this work?  Thanks.



--
Cleartag Software, Inc.
972 McMillan Avenue
Winnipeg, MB
R3M 0V7
(204) 284-9839 phone/cell
(204) 284-9838 fax
gwea...@cleartagsoftware.com

Fast. Accurate. Easy.

Re: Postgresql and VBA - Connection Timeout

[Adrian Klaver]

On 7/11/19 1:04 PM, Wayne Mell wrote:
Can someone help me with executing a postgres function from VBA?  No 
matter what function I call, it always times out after exactly 30 
seconds.  I've tried to use the statement_timeout a million different 
ways and always get an error that states "Cancelling statement due to 
statement timeout" in Access.


Here is the code I've been using to test:

Dim cnn As ADODB.Connection
Dim cnnCmd As ADODB.Command

  Set cnn = New ADODB.Connection
        cnn.ConnectionString = "Driver={PostgreSQL 
Unicode(x64)};Server=postgres.office.server;Port=5432;Database=DBS;UID=postgresuser;PWD=postgrespwd;"

        cnn.ConnectionTimeout = 0
    cnn.Open

Set cnnCmd = New ADODB.Command
cnnCmd.CommandTimeout = 0

Set cnnCmd = cnn.Execute("select pg_sleep(50);")

There are other functions that I've tried to execute, and they all work 
as long as they take less than 30 seconds to run.  I just started 
running pg_sleep to test.


If I log into pgAdmin with the same user name and password, and on the 
same machine, then the functions will execute no matter how long they 
take.  It's only through VBA and the psqlODBC driver that I have the 30 
second timeout limit.


Has anyone found a way to make this work?  Thanks.


Realized you are setting CommandTimeout = 0.

Maybe this thread will help?:
https://www.postgresql.org/message-id/1516895461336-0.post%40n3.nabble.com

Otherwise I would see if someone over on the -odbc list:

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

could help.







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

Download link for postgres 10.8 edb software

[Nanda Kumar]

Hello Team,

Currently we are using postgres database 9.7 version. We need to test our 
application in 10.8 edb version. So can you please share the download link for 
PostgreSQL 10.8 edb software. This is required for testing purpose .

Regards
Nanda Kumar.M
SmartStream Technologies
Eastland Citadel | 5th Floor | 102 Hosur Road | Banglore 560 095 | India
nanda.ku...@smartstream-stp.com | 
www.smartstream-stp.com
Mob

+91 99720 44779

Tel

+91 80617 64107




The information in this email is confidential and may be legally privileged. It 
is intended solely for the addressee. Access to this email by anyone else is 
unauthorised. If you are not the intended recipient, any disclosure, copying, 
distribution or any action taken or omitted to be taken in reliance on it, is 
prohibited and may be unlawful.

Re: Download link for postgres 10.8 edb software

[Michael Paquier]

On Fri, Jul 12, 2019 at 02:55:55AM +, Nanda Kumar wrote:
> Currently we are using postgres database 9.7 version.

You may be mistaken.  There is a major release of PostgreSQL called
9.6, but after that we have jumped directly to 10, reducing the number
of digits to mark a given minor version from 3 to 2.
--
Michael


signature.asc
Description: PGP signature

Re: Download link for postgres 10.8 edb software

[Thomas Kellerer]

Nanda Kumar schrieb am 12.07.2019 um 04:55:
> Currently we are using postgres database 9.7 version. We need to test
> our application in 10.8 edb version. So can you please share the
> download link for PostgreSQL 10.8 edb software. This is required for
> testing purpose .
> 

The current minor release for Postgres 10 is 10.9 you should use 10.8

You can download it from here:

   https://www.enterprisedb.com/downloads/postgres-postgresql-downloads

If you are running Linux, it is better to install it through your Linux package 
manager as described here:

   https://www.postgresql.org/download/

Thomas

Re: Download link for postgres 10.8 edb software

[Michael Paquier]

On Fri, Jul 12, 2019 at 05:57:10AM +, Nanda Kumar wrote:
> Its my bad. Yes, currently  we are using 9.6 version. Now we are
> looking for 10.8 edb software for testing purpose . Kindly advise
> where we can download the 10.8 edb software ?

10.9 is the latest version in the 10.X series:
https://www.postgresql.org/download/windows/
--
Michael


signature.asc
Description: PGP signature

Machine learning top of db logs

[Toomas Kristin]

Hi,

Basically seems that data science and machine learning are going to be more and 
more popular at every field of life. I have considered to use machine learning 
top of logs generated by PostgreSQL servers. However before I start maybe 
someone already has done that and can share some experience?

Toomas