Re: [GENERAL] wal_retrieve_retry_interval

[Ludovic Vaugeois-Pepin]

On Tue, May 30, 2017 at 12:20 AM, Michael Paquier
 wrote:
> On Sun, May 28, 2017 at 9:41 AM, Ludovic Vaugeois-Pepin
>  wrote:
>> On Sun, May 28, 2017 at 9:49 AM, Ludovic Vaugeois-Pepin
>>  wrote:
>>> It really is set at 3s on all servers (master and standbies) earlier in the 
>>> "deployment" process at the same time "listen_addresses", "hot_standby", 
>>> and others are set. This doesn't seem to happen every time I run tests. I 
>>> increased logging to DEBUG1. This is what I got when the problem occurred 
>>> again. Note that the empty line is there in the log file.
>
> Aren't those logs coming from a standby that is being reconnected to a
> promoted standby? In short you restarted this standby with a fresh
> recovery.conf.

Almost: the promoted standby (host test1) hasn't been started (and
promoted) when the standby with the 15 seconds delay (host test2)
tries to connect to it (its fresh recovery.conf):

$ cat recovery.conf
standby_mode = 'on'
recovery_target_timeline = 'latest'
primary_conninfo = 'host=test1 port=5432 user=repl1'
primary_slot_name = 'test2'


>The WAL receiver would try to fetch history file data
> for validity checks when it notices that there is a timeline jump,
> when it needs to itself jump to a new timeline, and the logs you are
> showing highlight that.

Logs below seem to indicate that the 15s delay begins before the
promoted standby is started. Is that the problem? Some network
connection timeout?




The standby (below as host test2) is trying to reconnect to a to-be
started and promoted standby (below as host test1).


host test2: before host1 is fully started and promoted:

< 2017-05-29 22:42:17.022 CEST > LOG:  database system is ready to
accept read only connections
< 2017-05-29 22:42:17.022 CEST > DEBUG:  checkpointer updated shared
memory configuration values
< 2017-05-29 22:42:17.022 CEST > DEBUG:  find_in_dynamic_libpath:
trying "/usr/pgsql-9.6/lib/libpqwalreceiver"
< 2017-05-29 22:42:17.022 CEST > DEBUG:  find_in_dynamic_libpath:
trying "/usr/pgsql-9.6/lib/libpqwalreceiver.so"
< 2017-05-29 22:42:17.025 CEST > FATAL:  could not connect to the
primary server: FATAL:  the database system is starting up
...
< 2017-05-29 22:42:17.026 CEST > DEBUG:  switched WAL source from
stream to archive after failure
< 2017-05-29 22:42:17.026 CEST > DEBUG:  invalid record length at
0/8000360: wanted 24, got 0
< 2017-05-29 22:42:17.026 CEST > DEBUG:  switched WAL source from
archive to stream after failure
... (15 seconds later) ...
< 2017-05-29 22:42:32.042 CEST > DEBUG:  switched WAL source from
stream to archive after failure
< 2017-05-29 22:42:32.042 CEST > DEBUG:  invalid record length at
0/8000360: wanted 24, got 0
< 2017-05-29 22:42:32.042 CEST > DEBUG:  switched WAL source from
archive to stream after failure
< 2017-05-29 22:42:32.043 CEST > DEBUG:  find_in_dynamic_libpath:
trying "/usr/pgsql-9.6/lib/libpqwalreceiver"
< 2017-05-29 22:42:32.043 CEST > DEBUG:  find_in_dynamic_libpath:
trying "/usr/pgsql-9.6/lib/libpqwalreceiver.so"
< 2017-05-29 22:42:32.047 CEST > LOG:  fetching timeline history file
for timeline 4 from primary server
< 2017-05-29 22:42:32.050 CEST > LOG:  started streaming WAL from
primary at 0/800 on timeline 3



host test1: to-be-promoted server where the 500ms
wal_retrieve_retry_interval seems to be honored (with a "bogus"
primary_conninfo = 'host= port=5432 user=repl1'):

< 2017-05-29 22:42:17.099 CEST > LOG:  database system is ready to
accept read only connections
< 2017-05-29 22:42:17.102 CEST > DEBUG:  find_in_dynamic_libpath:
trying "/usr/pgsql-9.6/lib/libpqwalreceiver"
< 2017-05-29 22:42:17.103 CEST > DEBUG:  find_in_dynamic_libpath:
trying "/usr/pgsql-9.6/lib/libpqwalreceiver.so"
< 2017-05-29 22:42:17.104 CEST > FATAL:  could not connect to the
primary server: could not translate host name "port=5432" to address:
Name or service not known

< 2017-05-29 22:42:17.104 CEST > DEBUG:  shmem_exit(1): 1
before_shmem_exit callbacks to make
< 2017-05-29 22:42:17.104 CEST > DEBUG:  shmem_exit(1): 4
on_shmem_exit callbacks to make
< 2017-05-29 22:42:17.104 CEST > DEBUG:  proc_exit(1): 2 callbacks to make
< 2017-05-29 22:42:17.104 CEST > DEBUG:  exit(1)
< 2017-05-29 22:42:17.104 CEST > DEBUG:  shmem_exit(-1): 0
before_shmem_exit callbacks to make
< 2017-05-29 22:42:17.104 CEST > DEBUG:  shmem_exit(-1): 0
on_shmem_exit callbacks to make
< 2017-05-29 22:42:17.104 CEST > DEBUG:  postgres child[5168]: starting with (
< 2017-05-29 22:42:17.104 CEST > DEBUG:  proc_exit(-1): 0 callbacks to make
< 2017-05-29 22:42:17.104 CEST > DEBUG: postgres
< 2017-05-29 22:42:17.104 CEST > DEBUG:  )
< 2017-05-29 22:42:17.104 CEST > DEBUG:  InitPostgres
< 2017-05-29 22:42:17.104 CEST > DEBUG:  my backend ID is 2
< 2017-05-29 22:42:17.104 CEST > DEBUG:  StartTransaction
< 2017-05-29 22:42:17.104 CEST > DEBUG:  name: unnamed; blockState:
   DEFAULT; state: INPROGR, xid/subid/cid: 0/1/0, nestlvl: 1,

Re: [GENERAL] wal_retrieve_retry_interval

[Michael Paquier]

On Sun, May 28, 2017 at 9:41 AM, Ludovic Vaugeois-Pepin
 wrote:
> On Sun, May 28, 2017 at 9:49 AM, Ludovic Vaugeois-Pepin
>  wrote:
>> It really is set at 3s on all servers (master and standbies) earlier in the 
>> "deployment" process at the same time "listen_addresses", "hot_standby", and 
>> others are set. This doesn't seem to happen every time I run tests. I 
>> increased logging to DEBUG1. This is what I got when the problem occurred 
>> again. Note that the empty line is there in the log file.

Aren't those logs coming from a standby that is being reconnected to a
promoted standby? In short you restarted this standby with a fresh
recovery.conf. The WAL receiver would try to fetch history file data
for validity checks when it notices that there is a timeline jump,
when it needs to itself jump to a new timeline, and the logs you are
showing highlight that.
-- 
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] Does NUMERIC lose precision?

[Tom Lane]

Kaiting Chen  writes:
> Hi everyone. I’m trying to perform some exact precision arithmetic with 
> PostgreSQL’s NUMERIC type. However I can’t seem to get the unparameterized 
> NUMERIC type to perform exact precision arithmetic:

Division is inherently inexact, except in special cases.  If you think
that 1/(2^64) should be carried out to enough digits to be exact,
what would you have us do with 1/3?

The actual behavior is that it will choose a result scale (number of
digits after the decimal point) that is dependent on the scales of the
input arguments, but not on their precise values.  I don't recall the
details beyond that.

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] Does NUMERIC lose precision?

[Melvin Davidson]

On Mon, May 29, 2017 at 4:19 PM, Kaiting Chen  wrote:

> Hi everyone. I’m trying to perform some exact precision arithmetic with
> PostgreSQL’s NUMERIC type. However I can’t seem to get the unparameterized
> NUMERIC type to perform exact precision arithmetic:
>
> # SELECT 2::NUMERIC ^ 64;
>?column?
> ---
>  18446744073709551616.
> (1 row)
>
> While the above operation works fine once I divide 1 by that number the
> result is an inexact decimal number:
>
> # SELECT 1 / (2::NUMERIC ^ 64);
> ?column?
> 
>  0.00054210108624275222
> (1 row)
>
> It doesn't seem to be an issue with the output either as taking the
> reciprocal yields a different number than I started with:
>
> # SELECT 1 / (1 / (2::NUMERIC ^ 64));
>  ?column?
> ---
>  18446744073709551514.042092759729171265910020841463748922
> (1 row)
>
> The only way to get an exact result is by specifying an explicit precision
> and scale:
>
> # SELECT 1 / (2::NUMERIC(96, 64) ^ 64);
>   ?column?
> 
>  0.000542101086242752217003726400434970855712890625
> (1 row)
>
> # SELECT 1 / (1 / (2::NUMERIC(96, 64) ^ 64));
>?column?
> 
> ---
>  18446744073709551616.00
> 00
> (1 row)
>
> However this does not seem intuitive from the documentation which states
> that:
>
> Specifying:
>
> NUMERIC
>
> without any precision or scale creates a column in which numeric values of
> any precision and scale can be stored, up to the implementation limit on
> precision. A column of this kind will not coerce input values to any
> particular scale...
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

>While the above operation works fine once I divide 1 by that number the
result is an inexact decimal number:
># SELECT 1 / (2::NUMERIC ^ 64);
  ?   ?column?
>
>0.00054210108624275222
>(1 row)


*That is the same answer you get when you use any calculator. *


*Are you sure you did not meanSELECT 2::NUMERIC^ 64/1;*


*?column?18446744073709551616.*

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

[GENERAL] Does NUMERIC lose precision?

[Kaiting Chen]

Hi everyone. I’m trying to perform some exact precision arithmetic with 
PostgreSQL’s NUMERIC type. However I can’t seem to get the unparameterized 
NUMERIC type to perform exact precision arithmetic:

# SELECT 2::NUMERIC ^ 64;
   ?column?
---
 18446744073709551616.
(1 row)

While the above operation works fine once I divide 1 by that number the result 
is an inexact decimal number:

# SELECT 1 / (2::NUMERIC ^ 64);
?column?

 0.00054210108624275222
(1 row)

It doesn't seem to be an issue with the output either as taking the reciprocal 
yields a different number than I started with:

# SELECT 1 / (1 / (2::NUMERIC ^ 64));
 ?column?  
---
 18446744073709551514.042092759729171265910020841463748922
(1 row)

The only way to get an exact result is by specifying an explicit precision and 
scale:

# SELECT 1 / (2::NUMERIC(96, 64) ^ 64);
  ?column?  

 0.000542101086242752217003726400434970855712890625
(1 row)

# SELECT 1 / (1 / (2::NUMERIC(96, 64) ^ 64));
   ?column? 
   
---
 
18446744073709551616.
(1 row)

However this does not seem intuitive from the documentation which states that:

Specifying:

NUMERIC

without any precision or scale creates a column in which numeric values of any 
precision and scale can be stored, up to the implementation limit on precision. 
A column of this kind will not coerce input values to any particular scale...


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

[PAWAN SHARMA]

On Mon, May 29, 2017 at 7:17 PM, Adrian Klaver 
wrote:

> On 05/29/2017 06:40 AM, PAWAN SHARMA wrote:
>
>>
>>
> ya, but how can fix it.??
>>
>> because memory is not an issue on both the server.
>>
>>
>> Hi Chris,
>>
>> I am able to get the output script in output.sql but now data migration
>> is the big problem.
>>
>
> http://ora2pg.darold.net/documentation.html#postgresql_import
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>


Thanks all,

now it's working After DATA_LIMIT15000 in ora2pg.conf.

test migration is successful.


C:\ora2pg>ora2pg -c ora2pg.conf
[>] 2/2 tables (100.0%) end of scanning.
[>] 0/2 tables (0.0%) end of scanning.
[>] 2/2 tables (100.0%) end of table export.
-- Generated by Ora2Pg, the Oracle database Schema converter, version 18.1
-- Copyright 2000-2017 Gilles DAROLD. All rights reserved.
-- DATASOURCE: dbi:Oracle:host=;sid=;port=1521

SET client_encoding TO 'UTF8';

\set ON_ERROR_STOP ON


CREATE TABLE mytab (
id bigint,
name varchar(30),
dt timestamp
) ;

CREATE TABLE test (
id bigint,
name varchar(30)
) ;
[>] 1/1 rows (100.0%) Table MYTAB (0 recs/sec)
[>] 1/2 total rows (50.0%) - (4 sec., avg: 0
recs/sec).
[>] 1/1 rows (100.0%) Table TEST (0 recs/sec)
[>] 2/2 total rows (100.0%) - (261 sec., avg: 0
recs/sec).
[>] 2/2 rows (100.0%) on total estimated data (262
sec., avg: 0 recs/sec)
C:\ora2pg>

[GENERAL] Memorial Day

[Melvin Davidson]

*On this Memorial Day, Let us not forget the many that gave their lives so
that we can enjoy our Freedom today. I give thanks to all my fellow
veterans, and those still on active duty. May future generations still
remember us and grow to never know the horrors of war.Da Nang 1969->'70*
-- 
*Melvin Davidson*
I reserve the right to fantasize.  Whether or not you
wish to share my fantasy is entirely up to you.

Re: [GENERAL] Oracle to PostgreSQL Migration.

[Adrian Klaver]

On 05/29/2017 06:40 AM, PAWAN SHARMA wrote:





ya, but how can fix it.??

because memory is not an issue on both the server.


Hi Chris,

I am able to get the output script in output.sql but now data migration 
is the big problem.


http://ora2pg.darold.net/documentation.html#postgresql_import

--
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] Oracle to PostgreSQL Migration.

[Neil Anderson]

>>>
>>>
>> ya, but how can fix it.??
>>
>> because memory is not an issue on both the server.
>>
>

I've never used ora2pg but there is some documentation saying that
often an out of memory issue can be fixed by adjusting your DATA_LIMIT
setting. https://github.com/darold/ora2pg/blob/master/README#L307

> Hi Chris,
>
> I am able to get the output script in output.sql but now data migration is
> the big problem.

-- 
Neil Anderson
n...@postgrescompare.com
https://www.postgrescompare.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] Oracle to PostgreSQL Migration.

[PAWAN SHARMA]

On Mon, May 29, 2017 at 6:54 PM, PAWAN SHARMA 
wrote:

>
>
> On Mon, May 29, 2017 at 6:51 PM, Chris Mair  wrote:
>
>> [>] 1/1 rows (100.0%) Table MYTAB (0 recs/sec)
>>> [>] 1/2 total rows (50.0%) - (4 sec., avg: 0
>>> recs/sec).
>>> Out of memory!] 1/2 rows (50.0%) on total estimated data (4
>>> sec., avg: 0 recs/sec)
>>> Issuing rollback() due to DESTROY without explicit disconnect() of
>>> DBD::Oracle::db handle (DESCRIPTION=(ADDRESS=(PO
>>> RT=1521)(PROTOCOL=tcp)(HOST=))(CONNECT_DATA=(SID=)))
>>> at C:/Strawberry/perl/vendor/
>>> lib/DBD/Oracle.pm line 348.
>>>
>>>
>>> So, It will show data enter in mytab 100% but in test it is 50%. but
>>> when I checked on PostgreSQL server their data only in one table.
>>>
>>
>> Did you notice the "Out of memory!" you got there?
>>
>> I guess that's the problem now...
>>
>> Bye,
>> Chris.
>>
>>
>>
>>
>>
>> ya, but how can fix it.??
>
> because memory is not an issue on both the server.
>
>
Hi Chris,

I am able to get the output script in output.sql but now data migration is
the big problem.

Re: [GENERAL] Oracle to PostgreSQL Migration.

[PAWAN SHARMA]

On Mon, May 29, 2017 at 6:51 PM, Chris Mair  wrote:

> [>] 1/1 rows (100.0%) Table MYTAB (0 recs/sec)
>> [>] 1/2 total rows (50.0%) - (4 sec., avg: 0
>> recs/sec).
>> Out of memory!] 1/2 rows (50.0%) on total estimated data (4
>> sec., avg: 0 recs/sec)
>> Issuing rollback() due to DESTROY without explicit disconnect() of
>> DBD::Oracle::db handle (DESCRIPTION=(ADDRESS=(PO
>> RT=1521)(PROTOCOL=tcp)(HOST=))(CONNECT_DATA=(SID=)))
>> at C:/Strawberry/perl/vendor/
>> lib/DBD/Oracle.pm line 348.
>>
>>
>> So, It will show data enter in mytab 100% but in test it is 50%. but when
>> I checked on PostgreSQL server their data only in one table.
>>
>
> Did you notice the "Out of memory!" you got there?
>
> I guess that's the problem now...
>
> Bye,
> Chris.
>
>
>
>
>
> ya, but how can fix it.??

because memory is not an issue on both the server.

Re: [GENERAL] Oracle to PostgreSQL Migration.

[Chris Mair]

[>] 1/1 rows (100.0%) Table MYTAB (0 recs/sec)
[>] 1/2 total rows (50.0%) - (4 sec., avg: 0 recs/sec).
Out of memory!] 1/2 rows (50.0%) on total estimated data (4 sec., 
avg: 0 recs/sec)
Issuing rollback() due to DESTROY without explicit disconnect() of 
DBD::Oracle::db handle (DESCRIPTION=(ADDRESS=(PO
RT=1521)(PROTOCOL=tcp)(HOST=))(CONNECT_DATA=(SID=))) at 
C:/Strawberry/perl/vendor/
lib/DBD/Oracle.pm line 348.


So, It will show data enter in mytab 100% but in test it is 50%. but when I 
checked on PostgreSQL server their data only in one table.


Did you notice the "Out of memory!" you got there?

I guess that's the problem now...

Bye,
Chris.








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

[PAWAN SHARMA]

On Mon, May 29, 2017 at 6:28 PM, Chris Mair  wrote:

> C:\ora2pg>ora2pg -c ora2pg.conf
>> [>] 2/2 tables (100.0%) end of scanning.
>> [>] 0/2 tables (0.0%) end of scanning.
>> [>] 2/2 tables (100.0%) end of table export.
>>
>
> Looks good so far.
> This means you could connect to Oracle DB now.
>
>
> DBD::Pg::st execute failed: ERROR:  relation "mytab" does not exist
>>
>
> This is coming from the Postgres side.
>
> In ora2pg.conf go to the section
>
> OUTPUT SECTION (Control output to file or PostgreSQL database)
>
> I suggest you comment out (prefix with #) the part
>
> #PG_DSN dbi:Pg:dbname=test_db;host=localhost;port=5432
> #PG_USERtest
> #PG_PWD test
>
> and just have ora2pg write its ouput to a file by setting OUTPUT like this:
>
> OUTPUT  output.sql
>
> This way you have your oputput for Postgres in a file that you can check
> out
> and try importing step by step. I guess you are running this on some test
> data, so the file will be small enough to open it with an editor.
> You cap paste piece by piece into a Postgres prompt (psql or pgadmin or
> whatever
> you're using).
>
> You can then see at what point you get an error (and hopefully understand
> what's happening).
>
> Bye,
> Chris.
>
>
>
Hi Chris,

Thanks for suggestion.!!!

here in ora2pg.conf, I have used below type in ora2pg.conf and create the
table manually on PostgreSQL server.


*TYPETABLE*


*output after this*
-
C:\ora2pg>ora2pg -c ora2pg.conf
Ora2Pg version: 18.1
Trying to connect to database:
dbi:Oracle:host=;sid=;port=1521
Isolation level: SET TRANSACTION ISOLATION LEVEL READ COMMITTED
Retrieving table information...
[1] Scanning table MYTAB (1 rows)...
[2] Scanning table TEST (1 rows)...
Dumping table TEST...
Dumping table MYTAB...
-- Generated by Ora2Pg, the Oracle database Schema converter, version 18.1
-- Copyright 2000-2017 Gilles DAROLD. All rights reserved.
-- DATASOURCE: dbi:Oracle:host=;sid=;port=1521

SET client_encoding TO 'UTF8';

\set ON_ERROR_STOP ON


CREATE TABLE test (
id bigint,
name varchar(30)
) ;

CREATE TABLE mytab (
id bigint,
name varchar(30),
dt timestamp
) ;


but, when i am trying to insert data using

TYPE   TABLE, INSERT


C:\ora2pg>ora2pg -c ora2pg.conf
[>] 2/2 tables (100.0%) end of scanning.
[>] 0/2 tables (0.0%) end of scanning.
[>] 2/2 tables (100.0%) end of table export.
-- Generated by Ora2Pg, the Oracle database Schema converter, version 18.1
-- Copyright 2000-2017 Gilles DAROLD. All rights reserved.
-- DATASOURCE: dbi:Oracle:host=;sid=;port=1521

SET client_encoding TO 'UTF8';

\set ON_ERROR_STOP ON


CREATE TABLE mytab (
id bigint,
name varchar(30),
dt timestamp
) ;

CREATE TABLE test (
id bigint,
name varchar(30)
) ;
[>] 1/1 rows (100.0%) Table MYTAB (0 recs/sec)
[>] 1/2 total rows (50.0%) - (4 sec., avg: 0
recs/sec).
Out of memory!] 1/2 rows (50.0%) on total estimated data (4
sec., avg: 0 recs/sec)
Issuing rollback() due to DESTROY without explicit disconnect() of
DBD::Oracle::db handle (DESCRIPTION=(ADDRESS=(PO
RT=1521)(PROTOCOL=tcp)(HOST=))(CONNECT_DATA=(SID=))) at
C:/Strawberry/perl/vendor/
lib/DBD/Oracle.pm line 348.


So, It will show data enter in mytab 100% but in test it is 50%. but when I
checked on PostgreSQL server their data only in one table.

test=# select * from mytab ;
 id | name | dt
+--+-
  1 | aa   | 2017-05-29 06:05:46
(1 row)

test=# select * from test;
 id | name
+--
(0 rows)




So, I am stuck here..!!! Please suggest.

Re: [GENERAL] Oracle to PostgreSQL Migration.

[Chris Mair]

C:\ora2pg>ora2pg -c ora2pg.conf
[>] 2/2 tables (100.0%) end of scanning.
[>] 0/2 tables (0.0%) end of scanning.
[>] 2/2 tables (100.0%) end of table export.


Looks good so far.
This means you could connect to Oracle DB now.



DBD::Pg::st execute failed: ERROR:  relation "mytab" does not exist


This is coming from the Postgres side.

In ora2pg.conf go to the section

OUTPUT SECTION (Control output to file or PostgreSQL database)

I suggest you comment out (prefix with #) the part

#PG_DSN dbi:Pg:dbname=test_db;host=localhost;port=5432
#PG_USERtest
#PG_PWD test

and just have ora2pg write its ouput to a file by setting OUTPUT like this:

OUTPUT  output.sql

This way you have your oputput for Postgres in a file that you can check out
and try importing step by step. I guess you are running this on some test
data, so the file will be small enough to open it with an editor.
You cap paste piece by piece into a Postgres prompt (psql or pgadmin or whatever
you're using).

You can then see at what point you get an error (and hopefully understand
what's happening).

Bye,
Chris.




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

[PAWAN SHARMA]

On Mon, May 29, 2017 at 2:49 PM, Chris Mair  wrote:

> I am facing below issue while running below command.
>>
>> *C:\ora2pg>ora2pg -c ora2pg.conf*
>> DBI connect('host=;sid=;port=',',...)
>> failed: ORA-01017: invalid username/p
>> assword; logon denied (DBD ERROR: OCISessionBegin) at
>> C:/Strawberry/perl/site/lib/Ora2Pg.pm line 1376.
>> FATAL: 1017 ... ORA-01017: invalid username/password; logon denied (DBD
>> ERROR: OCISessionBegin)
>> Aborting export...
>>
>> even I am able to login in Oracle with same  and 
>>
>
> If you can login from the same host using sqlplus like this:
>
> sqlplus myuser/mypass@myhost:1521/mysid
>
> then you likely can have ora2pg connect too. Just make sure that in
> ora2pg.conf you put the lines:
>
> ORACLE_DSN  dbi:Oracle:host=myhost;sid=mysid
> ORACLE_USER myuser
> ORACLE_PWD  mypass
>
> Bye,
> Chris.
>
>
>
Hi Chris,

C:\ora2pg>ora2pg -c ora2pg.conf
[>] 2/2 tables (100.0%) end of scanning.
[>] 0/2 tables (0.0%) end of scanning.
[>] 2/2 tables (100.0%) end of table export.
-- Generated by Ora2Pg, the Oracle database Schema converter, version 18.1
-- Copyright 2000-2017 Gilles DAROLD. All rights reserved.
-- DATASOURCE: dbi:Oracle:host=;sid=;port=1521

SET client_encoding TO 'UTF8';

\set ON_ERROR_STOP ON


CREATE TABLE test (
id bigint,
name varchar(30)
) ;

CREATE TABLE mytab (
id bigint,
name varchar(30),
dt timestamp
) ;
DBD::Pg::st execute failed: ERROR:  relation "mytab" does not exist
LINE 1: INSERT INTO mytab (id,name,dt) VALUES ($1,$2,$3)
^ at C:/Strawberry/perl/site/lib/Ora2Pg.pm line 11574.
FATAL: ERROR:  relation "mytab" does not exist
LINE 1: INSERT INTO mytab (id,name,dt) VALUES ($1,$2,$3)
^
Aborting export...

C:\ora2pg>

Re: [GENERAL] Oracle to PostgreSQL Migration.

[Chris Mair]

I am facing below issue while running below command.

*C:\ora2pg>ora2pg -c ora2pg.conf*
DBI connect('host=;sid=;port=',',...) failed: 
ORA-01017: invalid username/p
assword; logon denied (DBD ERROR: OCISessionBegin) at 
C:/Strawberry/perl/site/lib/Ora2Pg.pm line 1376.
FATAL: 1017 ... ORA-01017: invalid username/password; logon denied (DBD ERROR: 
OCISessionBegin)
Aborting export...

even I am able to login in Oracle with same  and 


If you can login from the same host using sqlplus like this:

sqlplus myuser/mypass@myhost:1521/mysid

then you likely can have ora2pg connect too. Just make sure that in ora2pg.conf 
you put the lines:

ORACLE_DSN  dbi:Oracle:host=myhost;sid=mysid
ORACLE_USER myuser
ORACLE_PWD  mypass

Bye,
Chris.




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

[PAWAN SHARMA]

Hi All,

I am going to migrate Oracle database into PostgreSQL using ora2pg tools.

I have installed Strawberry Perl.

C:\ora2pg>perl -v
This is perl 5, version 24, subversion 1 (v5.24.1) built for
MSWin32-x64-multi-thread
Copyright 1987-2017, Larry Wall


I am facing below issue while running below command.

*C:\ora2pg>ora2pg -c ora2pg.conf*
DBI connect('host=;sid=;port=',',...)
failed: ORA-01017: invalid username/p
assword; logon denied (DBD ERROR: OCISessionBegin) at
C:/Strawberry/perl/site/lib/Ora2Pg.pm line 1376.
FATAL: 1017 ... ORA-01017: invalid username/password; logon denied (DBD
ERROR: OCISessionBegin)
Aborting export...

even I am able to login in Oracle with same  and 

*entry in sqlnet.ora*

SQLNET.AUTHENTICATION_SERVICES = (NONE)
NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)
SQLNET.ALLOWED_LOGON_VERSION_CLIENT=8
SQLNET.ALLOWED_LOGON_VERSION_SERVER=8

Please suggest.!!

-Pawan

Re: [GENERAL] plpgsql function with offset - Postgres 9.1

[Albe Laurenz]

Patrick B wrote:
> I am running a background task on my DB, which will copy data from tableA to 
> tableB. For
> that, I'm writing a PL/PGSQL function which basically needs to do the 
> following:
> 
> 
> 1.Select the data from tableA
> 2.The limit will be put when calling the function
> 3.insert the selected data on Step 1 onto new table
> 
> Question:
> 
> * When I stop it and start it again, how can the query "know" that it has 
> already
> processed some rows so it won't do it twice on the same rows? If it stopped 
> on row number
> 100, I need it to continue on row number 101, for example.
> 
> * How can I ask the function to return the number of processed rows?
> 
> 
> I can add a column on TableB if needed, but not on tableA.
> 
> This is what I've done so far:

>   CREATE or REPLACE FUNCTION data_copy(rows integer)
>   RETURNS SETOF bigint AS $$

CREATE or REPLACE FUNCTION data_copy(p_limit integer, p_offset integer)
RETURNS integer;

>   declare
>   row record;
>   offset_num integer;

num_rows integer := 0;

>   BEGIN
>   FOR row IN EXECUTE '
>   SELECT
>   id,
>   path,
>   name,
>   name_last,
>   created_at
>   FROM
>   tablea
>   WHERE
>   ready = true
>   ORDER BY 1 LIMIT ' || rows || ' OFFSET ' || rows || 
> ''

'... LIMIT ' || p_limit || ' OFFSET ' || p_offset

>   LOOP

num_rows := num_rows + 1;

>   INSERT INTO tableB (id,path,name,name_last,created_at)
>   VALUES (row.id,row.path,row.name,row.name_last,row.created_at);
> 
>   END LOOP;

RETURN num_rows;

>   END
>   $$ language 'plpgsql';

There are two problems with this approach:

1. It will do the wrong thing if rows are added or deleted in "tablea" while
   you process it.

2. Queries with hight OFFSET values have bad performance.

The solution is to avoid OFFSET and to use "keyset pagination":
http://use-the-index-luke.com/no-offset

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