Re: Migration from SQL Server to PostgeSQL

[Pavel Stehule]

po 30. 11. 2020 v 7:06 odesílatel Muthukumar.GK 
napsal:

> Hi Pavel,
>
> Kindly let me know the following functionality can be possible in
> postgresql. If possible,provide me with a sample program.
>
> 1. Call the procedure from/inside another procedure  and insert the result
> set into Temp Table.
>

it is possible

2. Creating Dynamic procedure in postgresql. because We have dynamic
> procedure in SQL server, On execution of this  Procedure, another two
> procedures will be Created dynamically.
>

I don't know what dynamic procedure is.



>
> Regards
> Muthu
>
> On Mon, Nov 30, 2020 at 11:02 AM Pavel Stehule 
> wrote:
>
>>
>>
>> po 30. 11. 2020 v 6:12 odesílatel Muthukumar.GK 
>> napsal:
>>
>>> HiTeam,
>>>
>>> We need to migrate tables,Records,Procedure and function from SQL server
>>> to Postgresql. Kindly let me know, Is there any  tool or easiest way
>>> available to migrate from SQL Server to Postgresql. Please let me know if
>>> there are any limitations in *postgresql *compared to SQL server.
>>>
>>
>> The migration from MSSQL can be difficult, and needs a lot of manual
>> work. The concept of procedures is very different. In MSSQL is natural
>> returning multirecordset from procedures. This is not possible in Postgres,
>> where design of procedures (or all design) is similar to Oracle. It is
>> possible. I know some customers that did successful migrations from Sybase
>> to Postgres (Sybase had the same roots like MSSQL).
>>
>> Regards
>>
>> Pavel
>>
>>
>>> Regards
>>> Muthu.
>>>
>>

Re: Migration from SQL Server to PostgeSQL

[Muthukumar.GK]

Hi Pavel,

Kindly let me know the following functionality can be possible in
postgresql. If possible,provide me with a sample program.

1. Call the procedure from/inside another procedure  and insert the result
set into Temp Table.
2. Creating Dynamic procedure in postgresql. because We have dynamic
procedure in SQL server, On execution of this  Procedure, another two
procedures will be Created dynamically.


Regards
Muthu

On Mon, Nov 30, 2020 at 11:02 AM Pavel Stehule 
wrote:

>
>
> po 30. 11. 2020 v 6:12 odesílatel Muthukumar.GK 
> napsal:
>
>> HiTeam,
>>
>> We need to migrate tables,Records,Procedure and function from SQL server
>> to Postgresql. Kindly let me know, Is there any  tool or easiest way
>> available to migrate from SQL Server to Postgresql. Please let me know if
>> there are any limitations in *postgresql *compared to SQL server.
>>
>
> The migration from MSSQL can be difficult, and needs a lot of manual work.
> The concept of procedures is very different. In MSSQL is natural returning
> multirecordset from procedures. This is not possible in Postgres, where
> design of procedures (or all design) is similar to Oracle. It is possible.
> I know some customers that did successful migrations from Sybase to
> Postgres (Sybase had the same roots like MSSQL).
>
> Regards
>
> Pavel
>
>
>> Regards
>> Muthu.
>>
>

Re: Migration from SQL Server to PostgeSQL

[Pavel Stehule]

po 30. 11. 2020 v 6:12 odesílatel Muthukumar.GK 
napsal:

> HiTeam,
>
> We need to migrate tables,Records,Procedure and function from SQL server
> to Postgresql. Kindly let me know, Is there any  tool or easiest way
> available to migrate from SQL Server to Postgresql. Please let me know if
> there are any limitations in *postgresql *compared to SQL server.
>

The migration from MSSQL can be difficult, and needs a lot of manual work.
The concept of procedures is very different. In MSSQL is natural returning
multirecordset from procedures. This is not possible in Postgres, where
design of procedures (or all design) is similar to Oracle. It is possible.
I know some customers that did successful migrations from Sybase to
Postgres (Sybase had the same roots like MSSQL).

Regards

Pavel


> Regards
> Muthu.
>

Migration from SQL Server to PostgeSQL

[Muthukumar.GK]

HiTeam,

We need to migrate tables,Records,Procedure and function from SQL server to
Postgresql. Kindly let me know, Is there any  tool or easiest way
available to migrate from SQL Server to Postgresql. Please let me know if
there are any limitations in *postgresql *compared to SQL server.

Regards
Muthu.

Re: postgres_fdw insert extremely slow

[Craig Ringer]

On Thu, Nov 26, 2020 at 12:37 AM Mats Julian Olsen 
wrote:

>
> We have just set up postgres_fdw between two postgres databases, x and y,
> with the plan to periodically insert data from x into y.
>
> We've successfully set up the connection with a few options:
> `use_remote_estimate 'true'` and `fetch_size '5'` (the latter we've
> played around with). We've run ANALYZE on the foreign server.
>
> SELECTs against the foreign table returns in milliseconds, however an
> INSERT of 100 rows takes 10 seconds. we have roughly 200 000 rows for the
> initial sync, which translates into ~6 hours.
>
> Is this the expected performance of postgre_fdw? Is there anything we've
> overlooked when setting this up? Very curious to hear experiences from the
> community when doing read/write and not just read from foreign sources.
>

Are your inserts run in individual transactions or grouped into one
transaction? If the latter, commit time will be a factor.

What's the round-trip time (ping time) to the foreign server? Since
postgres_fdw runs each individual insert as a separate statement, you're
going to face insert times of (n * RTT) for inserts. Assuming negligible
time for insert execution on the foreign server, your runtime is 21600
seconds for 20 rows, i.e. 9.25 rows/second or 0.108 seconds/row. That
would be consistent with a 90-100ms ping time to the foreign server.

You'll be pleased to know that there is currently work ongoing in
pgsql-hackers to add the capability to batch INSERTs in postgres_fdw to
improve performance on higher latency links. See
https://www.postgresql.org/message-id/flat/20200628151002.7x5laxwpgvkyiu3q%40development
. That could well reduce your RTTs immensely. Try the patch out if you can
and report back please.

If you can get the client application to manage the foreign insert
directly, then handle commit consistency using two-phase commit, you should
be able to do the insert in half an hour or less instead (assuming ~10ms
execution time per insert and 90ms RTT). If you use `COPY`, or if you can
use JDBC to benefit from PgJDBC's support for the JDBC addBatch() and
executeBatch() APIs, you should be able to get it down way lower than that.
Assuming your RTT latency is 90ms and you spend 10ms executing each insert,
your insert time might well go down to 0.010 * 20 + 90*2 = 2180 seconds
or about 36 minutes. If you can insert a row in 3ms with COPY, 13 minutes.

There's work ongoing on making libpq (which underlies postgres_fdw) capable
of running multiple statements at the same time, i.e. "pipelining". That
won't immediately benefit postgres_fdw because using it in postgres_fdw
would require changes to the whole postgres executor as well. But if
adopted, it'd allow postgres_fdw to achieve that sort of performance
transparently.

Re: "Server versions prior to 8.0 do not support savepoints" when using JDBC ...

[Martin Goodson]

On 29/11/2020 18:46, David G. Johnston wrote:

On Sun, Nov 29, 2020 at 11:06 AM Martin Goodson 
wrote:


Hello.

I wonder if anyone can assist with this?

Some of my developers are reporting that they are getting the following
message when attempting to connect to the database using jdbc:

Caused by: org.postgresql.util.PSQLException: Server versions prior to
8.0 do not support savepoints

at


org.postgresql.jdbc3.AbstractJdbc3Connection.setSavepoint(AbstractJdbc3Connection.java:95)

Database version is 10.5 running under RHEL 7.4. The postgreSQL jdbc
driver version they're employing is postgresl-42.2.9.



My best guess is that they are not actually running 42.2.9, they are
running an older version that was released before PostgreSQL v10 came out
and the version detection code doesn't recognize the new version format
since 10.

I didn't dig into this too deeply but I did download the zip for 42.2.9 and
those classes don't even exist at that point since the build was re-done
using pre-processors.

David J.

Oh. That's very interesting. Thank you, David. I will chase up with the 
developers tomorrow to confirm with them the version of the postgres 
jdbc driver they are using.


I'm no coder, it didn't occur to me to actually check with the source. 
Nice one :)


Many thanks.

Regards,

Martin.

--
Martin Goodson

Whatever you've got planned, forget it. I'm the Doctor.
I'm 904 years old. I'm from the planet Gallifrey in the
constellation of Kasterborous. I am The Oncoming Storm,
the Bringer of Darkness and you are ... basically just a
rabbit, aren't you? OK, carry on, just a general ...
warning.

Re: "Server versions prior to 8.0 do not support savepoints" when using JDBC ...

[David G. Johnston]

On Sun, Nov 29, 2020 at 11:06 AM Martin Goodson 
wrote:

> Hello.
>
> I wonder if anyone can assist with this?
>
> Some of my developers are reporting that they are getting the following
> message when attempting to connect to the database using jdbc:
>
> Caused by: org.postgresql.util.PSQLException: Server versions prior to
> 8.0 do not support savepoints
>
> at
>
>
> org.postgresql.jdbc3.AbstractJdbc3Connection.setSavepoint(AbstractJdbc3Connection.java:95)
>
> Database version is 10.5 running under RHEL 7.4. The postgreSQL jdbc
> driver version they're employing is postgresl-42.2.9.
>

My best guess is that they are not actually running 42.2.9, they are
running an older version that was released before PostgreSQL v10 came out
and the version detection code doesn't recognize the new version format
since 10.

I didn't dig into this too deeply but I did download the zip for 42.2.9 and
those classes don't even exist at that point since the build was re-done
using pre-processors.

David J.

Re: "Server versions prior to 8.0 do not support savepoints" when using JDBC ...

[Martin Goodson]

On 29/11/2020 18:28, Adrian Klaver wrote:

On 11/29/20 10:22 AM, Martin Goodson wrote:

On 29/11/2020 18:10, Adrian Klaver wrote:

On 11/29/20 10:06 AM, Martin Goodson wrote:

Hello.

I wonder if anyone can assist with this?

Some of my developers are reporting that they are getting the 
following message when attempting to connect to the database using 
jdbc:


Caused by: org.postgresql.util.PSQLException: Server versions prior 
to 8.0 do not support savepoints


at

org.postgresql.jdbc3.AbstractJdbc3Connection.setSavepoint(AbstractJdbc3Connection.java:95) 



Database version is 10.5 running under RHEL 7.4. The postgreSQL jdbc 
driver version they're employing is postgresl-42.2.9.



How was the database installed and from where?


Hello, Adrian. The database was installed using an enterprisedb 
installer, to its own dedicated set of directories.



Do you have more then one instance of Postgres running on the machine?


We do not, no. It is the sole PostgreSQL database cluster running on 
that box.



What is the connection code being used?


An excellent question. I will chase up with them, and find out. I 
believe they're using tomcat, but don't know the exact code involved.


FWIW the connection is not local, the attempted connection is from a 
different server.


And you know that this is actually the server being reached?

Do you see the attempted connection in the logs?


I do, yes. We log connections, and I can see connections incoming from 
their application servers.


At the moment we only log connections and erroring statements, but 
tomorrow I think I'll be temporarily enabling logging of all 
disconnections and all statements, to see what (if anything) they're 
actually executing on the database side before they see their error ...


Regards,

Martin.

-
Martin Goodson

Whatever you've got planned, forget it. I'm the Doctor.
I'm 904 years old. I'm from the planet Gallifrey in the
constellation of Kasterborous. I am The Oncoming Storm,
the Bringer of Darkness and you are ... basically just a
rabbit, aren't you? OK, carry on, just a general ...
warning.

Re: "Server versions prior to 8.0 do not support savepoints" when using JDBC ...

[Adrian Klaver]

On 11/29/20 10:22 AM, Martin Goodson wrote:

On 29/11/2020 18:10, Adrian Klaver wrote:

On 11/29/20 10:06 AM, Martin Goodson wrote:

Hello.

I wonder if anyone can assist with this?

Some of my developers are reporting that they are getting the 
following message when attempting to connect to the database using jdbc:


Caused by: org.postgresql.util.PSQLException: Server versions prior 
to 8.0 do not support savepoints


at

org.postgresql.jdbc3.AbstractJdbc3Connection.setSavepoint(AbstractJdbc3Connection.java:95) 



Database version is 10.5 running under RHEL 7.4. The postgreSQL jdbc 
driver version they're employing is postgresl-42.2.9.



How was the database installed and from where?


Hello, Adrian. The database was installed using an enterprisedb 
installer, to its own dedicated set of directories.



Do you have more then one instance of Postgres running on the machine?


We do not, no. It is the sole PostgreSQL database cluster running on 
that box.



What is the connection code being used?


An excellent question. I will chase up with them, and find out. I 
believe they're using tomcat, but don't know the exact code involved.


FWIW the connection is not local, the attempted connection is from a 
different server.


And you know that this is actually the server being reached?

Do you see the attempted connection in the logs?



Regards,

Martin.




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

Re: "Server versions prior to 8.0 do not support savepoints" when using JDBC ...

[Martin Goodson]

On 29/11/2020 18:10, Adrian Klaver wrote:

On 11/29/20 10:06 AM, Martin Goodson wrote:

Hello.

I wonder if anyone can assist with this?

Some of my developers are reporting that they are getting the 
following message when attempting to connect to the database using jdbc:


Caused by: org.postgresql.util.PSQLException: Server versions prior to 
8.0 do not support savepoints


at

org.postgresql.jdbc3.AbstractJdbc3Connection.setSavepoint(AbstractJdbc3Connection.java:95) 



Database version is 10.5 running under RHEL 7.4. The postgreSQL jdbc 
driver version they're employing is postgresl-42.2.9.



How was the database installed and from where?


Hello, Adrian. The database was installed using an enterprisedb 
installer, to its own dedicated set of directories.



Do you have more then one instance of Postgres running on the machine?


We do not, no. It is the sole PostgreSQL database cluster running on 
that box.



What is the connection code being used?


An excellent question. I will chase up with them, and find out. I 
believe they're using tomcat, but don't know the exact code involved.


FWIW the connection is not local, the attempted connection is from a 
different server.


Regards,

Martin.

--
Martin Goodson

Whatever you've got planned, forget it. I'm the Doctor.
I'm 904 years old. I'm from the planet Gallifrey in the
constellation of Kasterborous. I am The Oncoming Storm,
the Bringer of Darkness and you are ... basically just a
rabbit, aren't you? OK, carry on, just a general ...
warning.

Re: "Server versions prior to 8.0 do not support savepoints" when using JDBC ...

[Adrian Klaver]

On 11/29/20 10:06 AM, Martin Goodson wrote:

Hello.

I wonder if anyone can assist with this?

Some of my developers are reporting that they are getting the following 
message when attempting to connect to the database using jdbc:


Caused by: org.postgresql.util.PSQLException: Server versions prior to 
8.0 do not support savepoints


at

org.postgresql.jdbc3.AbstractJdbc3Connection.setSavepoint(AbstractJdbc3Connection.java:95) 



Database version is 10.5 running under RHEL 7.4. The postgreSQL jdbc 
driver version they're employing is postgresl-42.2.9.


How was the database installed and from where?

Do you have more then one instance of Postgres running on the machine?

What is the connection code being used?




The database itself has absolutely no issues creating savepoints (I 
checked it, just in case ...) so I'm assuming it must be some issue with 
the driver / how the driver has been configured.


As you can see I've not been given a a lot to go on, unfortunately. Has 
anyone seen this before? Is this a common issue and my google-fu has 
failed me? :)


Many thanks.

Regards,

M.




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

"Server versions prior to 8.0 do not support savepoints" when using JDBC ...

[Martin Goodson]

Hello.

I wonder if anyone can assist with this?

Some of my developers are reporting that they are getting the following 
message when attempting to connect to the database using jdbc:


Caused by: org.postgresql.util.PSQLException: Server versions prior to 
8.0 do not support savepoints


at

org.postgresql.jdbc3.AbstractJdbc3Connection.setSavepoint(AbstractJdbc3Connection.java:95)

Database version is 10.5 running under RHEL 7.4. The postgreSQL jdbc 
driver version they're employing is postgresl-42.2.9.


The database itself has absolutely no issues creating savepoints (I 
checked it, just in case ...) so I'm assuming it must be some issue with 
the driver / how the driver has been configured.


As you can see I've not been given a a lot to go on, unfortunately. Has 
anyone seen this before? Is this a common issue and my google-fu has 
failed me? :)


Many thanks.

Regards,

M.

--
Martin Goodson

Whatever you've got planned, forget it. I'm the Doctor.
I'm 904 years old. I'm from the planet Gallifrey in the
constellation of Kasterborous. I am The Oncoming Storm,
the Bringer of Darkness and you are ... basically just a
rabbit, aren't you? OK, carry on, just a general ...
warning.

Re: pgdump version mismatch error. server version: 13.0; pg_dump version: 12.4

[Adrian Klaver]

On 11/28/20 9:32 PM, mark armon wrote:


where to get pg_dump 13.0?


Are both Postgres instances(12.4 and 13.0) on the same machine?

If they are and you used the EDB installer the binaries(pg_dump for 
instance) will be under:


C:\Program File\PostgreSQL\\bin

If they are not then you will either have to use the version 13 of 
pg_dump that is on the other machine or install version 13 of Postgres 
on your machine.


Also be aware that if you are trying to dump a version 13 instance and 
restore it to a version 12 instance of Postgres you will probably have 
issues beyond that of the pg_dump version. The dump/restore process is 
not designed to go backwards.




On Sat, Nov 28, 2020 at 9:47 PM Adrian Klaver > wrote:


On 11/28/20 8:12 AM, mark armon wrote:
 >
 > OS: Windows
 > issue:
 >

https://stackoverflow.com/questions/14168920/how-to-fix-pg-dump-version-mismatch-errors



 >

>
 >
 > |pg_dump: error: server version: 13.0; pg_dump version: 12.4
pg_dump:
 > error: aborting because of server version mismatch |
 >

You are trying to dump a Postgres 13.0 version instance using the 12.4
version of pg_dump. That will not work as the error says. You need to
find and use the 13.0 version of pg_dump  and use that. FYI, it will
work the other way e.g. a newer version of pg_dump can dump an older
version of Postgres.


-- 
Adrian Klaver

adrian.kla...@aklaver.com 




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

Re: How to debug authentication issues in Postgres

[Daniele Varrazzo]

IIRC you need libpq at least 10 to use password encryption other than md5.
Maybe your java client uses an older version, or no libpq at all and the
client library misses that feature?

-- Daniele


On Sat, 28 Nov 2020, 16:12 Hemil Ruparel, 
wrote:

> I am unable to connect using Java in general. And DataGrip runs on Java as
> far as I know. My backend in python runs perfectly fine using the psycopg2
> library (postgres driver for python).
>
> I was actually changing the database name and user name
>
> On Sat, Nov 28, 2020 at 9:28 PM Adrian Klaver 
> wrote:
>
>> On 11/28/20 6:10 AM, Hemil Ruparel wrote:
>> > Line 88 is this line: hostdatabaseuser 0.0.0.0/0
>> >    scram-sha-256.
>> >
>> > I might have forgotten to change one of the names in the earlier mails.
>> >
>>
>> Change from what? This should just be a copy and paste or am I missing
>> something?
>>
>>
>> --
>> Adrian Klaver
>> adrian.kla...@aklaver.com
>>
>