Re: [EXT]Re: running \copy through perl dbi ?

[Johnson, Bruce E - (bjohnson)]

On Dec 10, 2023, at 10:41 AM, Vincent Veyron 
mailto:vv.li...@wanadoo.fr>> wrote:

External Email

On Fri, 8 Dec 2023 10:45:28 -0500
David Gauthier mailto:dfgpostg...@gmail.com>> wrote:

I'm trying to run a PG client side "\copy" command from a perl script.  I
tried using $dbh->do("\\copy ...") but it barffed when it saw the '\'...
ERROR:  syntax error at or near "\"

I can do this with a command line approach, attaching to the DB  then run
using...

Duh! I just realized that what I proposed with system() is a command line 
approach.

As David Johnston mentionned, you can use the SQL COPY command.

One thing to remember with the Perl DBI is that you can use a string variable 
in the $dbh->do() command.

Perl uses 2 different string variable delimiters:

1)  ‘ ‘ , which is exactly what you enter $s= ‘\copy * from foo as json’; will 
send that to the database without the need for escaping anything (unless you 
need to enter an actual ‘ in the command, in which case method two is better)

2) “ “ , which allows for declared perl variables to be substituted in the 
string:$table=‘foo’;$type=‘json’;$cmd=‘\copy’;$s= “$cmd * from $table as $type”;

Concatenation (periods between strings) works as well: $s = ‘\copy ‘.”* from 
foo as json”;

 Then $dbh->do($s); will work in alll three cases.

Been using perl and DBI for (does quick math, ulp!) over 20 years now wrangling 
a lot of things like this.


--
Bruce Johnson
University of Arizona
College of Pharmacy
Information Technology Group

Institutions do not have opinions, merely customs

Connection fails on one system in a address range allowed to connect

[Johnson, Bruce E - (bjohnson)]

I am migrating an existing web application from Oracle to postgres and I’m 
testing the connectivity.

Trying to run a test program (that works on another system in the same subnet!) 
I get this error:

Error system:

[root@dhbroomscheduling4 ~]# ./pg_test.pl 
DBI 
connect('dbname=webdata;host=dhbpostgres.pharmacy.arizona.edu;port=5432','trav',...)
 failed: FATAL:  password authentication failed for user "trav"
FATAL:  no pg_hba.conf entry for host "150.135.124.50", user "trav", database 
"webdata", no encryption at ./pg_test.pl line 8.

Working system:

[root@avipg perl]# ./pg_test.pl
Sector Alpha Crucis has 44 worlds  
Sector Antares has 37 worlds  
Sector Core has 221 worlds …

(The test dataset is a collection of mapping data for an old RPG game) 

Note the pg_test.pl script was copied from the working server to the non 
working one.

The pg_hba.conf on the server includes this which should encompass all systems 
in this VLAN

# external 'OldMTM' site range
hostssl all all 150.135.124.0/25 password

Another system in the same address range is working just fine with the 
identical setup, in fact it’s in production without issues. 

Both systems are running Rocky Linux 9, using the perl DBI interface with 
DBD::Pg all installed from the Rocky Linux repositories.

Firewall settings, Perl version, env variables etc are the same on both client 
hosts

I know the password is correct because I just successfully logged in on the 
server with psql -U trav -d webdata -W  and used the password in the connect 
statement in the script.

Anywhere else that I should look for a cause?


-- 
Bruce Johnson
University of Arizona
College of Pharmacy
Information Technology Group

Institutions do not have opinions, merely customs

Re: [EXT]Re: Strange error trying to import with Ora2PG

[Johnson, Bruce E - (bjohnson)]

On Oct 4, 2023, at 8:38 AM, Adrian Klaver  wrote:



If you search the source file directory for "reserver_visible_restriction" is 
there another file that contains it?



Sigh…that was it…Thank you.

There are two ‘views’ directories, one under ’sources/views’ and the other 
under /schema/views. Clearly the script is running the other one.


~/oracle_migration$ tree calendar3

calendar3

├── config

│   ├── ora2pg.conf

│   └── ora2pg.conf.orig


SNIP


├── schema

│   ├── dblinks

│


SNIP


│   └── views

│   ├── AHSC_SCHEDULE_ROUNDED_view.sql

│   ├── COP_COLLEGE_HOUR_view.sql

│   ├── RESERVER_VISIBLE_RESTRICTION_view.sql

│   ├── RESOURCEAFFIL_WITHCODE_view.sql

│   ├── UA_COLLEGE_LOOKUP_view.sql

│   └── view.sql

└── sources



SNIP


└── views

├── AHSC_SCHEDULE_ROUNDED_view.sql

├── COP_COLLEGE_HOUR_view.sql

├── RESERVER_VISIBLE_RESTRICTION_view.sql

├── RESOURCEAFFIL_WITHCODE_view.sql

├── UA_COLLEGE_LOOKUP_view.sql

└── view.sql


29 directories, 127 files


--
Bruce Johnson
University of Arizona
College of Pharmacy
Information Technology Group

Institutions do not have opinions, merely customs

Re: [EXT]Re: Strange error trying to import with Ora2PG

[Johnson, Bruce E - (bjohnson)]

That’s not it. export_schema.sh writes the scripts, and then emits a ora2pg 
command to pull in the data (I've managed to do this a dozen times so far 
working out how to do things. :-)

The dates on the files in the sources directory are consistent with that, and 
don’t change when I run the script.

-rw-rw-r-- 1 johnson johnson 872 Sep 27 22:02 AHSC_SCHEDULE_ROUNDED_view.sql
-rw-rw-r-- 1 johnson johnson 817 Sep 27 22:02 COP_COLLEGE_HOUR_view.sql
-rw-rw-r-- 1 johnson johnson 628 Oct  3 17:06 
RESERVER_VISIBLE_RESTRICTION_view.sql
-rw-rw-r-- 1 johnson johnson 515 Sep 27 22:02 RESOURCEAFFIL_WITHCODE_view.sql
-rw-rw-r-- 1 johnson johnson 535 Sep 27 22:02 UA_COLLEGE_LOOKUP_view.sql



On Oct 3, 2023, at 2:27 PM, Adrian Klaver 
mailto:adrian.kla...@aklaver.com>> wrote:

External Email

On 10/3/23 10:32, Johnson, Bruce E - (bjohnson) wrote:
I am trying to import an oracle schema with ora2pg and running into an odd 
error when the import_all.sh script gets to a specific view.
The error is:

However when I re-run the import_all.sh script it errors out at the same place 
with the same error (in fact the pasted error code is from running the script 
AFTER I fixed it). The sql isn’t being cached anywhere in the system is it?

I'm betting that every time you run import_all.sh it rewrites the scripts. 
Pretty sure if you look at the file the creation script will have been 
rewritten to its original from.

I know that I modified the correct file.
It happens even if I drop the database and recreate it.
--
Bruce Johnson
University of Arizona
College of Pharmacy
Information Technology Group
Institutions do not have opinions, merely customs

--
Adrian Klaver
adrian.kla...@aklaver.com<mailto:adrian.kla...@aklaver.com>


--
Bruce Johnson
University of Arizona
College of Pharmacy
Information Technology Group

Institutions do not have opinions, merely customs

Strange error trying to import with Ora2PG

[Johnson, Bruce E - (bjohnson)]

I am trying to import an oracle schema with ora2pg and running into an odd 
error when the import_all.sh script gets to a specific view.

The error is:

psql:schema/views/RESERVER_VISIBLE_RESTRICTION_view.sql:10: ERROR:  operator 
does not exist: character = integer
LINE 1: ...r.resource_id = rg.resource_id and r.requester_vis_only = 1;

A simple enough fix; Oracle happily accepts a number-like string as a number, 
and Postgres wants a string delimited,  so I went into the ./sources/views 
folder and edited the view creation to add the required single quotes:

-- Generated by Ora2Pg, the Oracle database Schema converter, version 24.0
-- Copyright 2000-2023 Gilles DAROLD. All rights reserved.
-- DATASOURCE: 
dbi:Oracle:host=dhboracle2.pharmacy.arizona.edu;sid=phmweb2;port=1521

SET client_encoding TO 'UTF8';

SET search_path = calendar3,oracle,public;
\set ON_ERROR_STOP ON

CREATE OR REPLACE VIEW reserver_visible_restriction (resource_id, 
resourcegroup_id, affil_id) AS select r.resource_id, rg.resourcegroup_id, 
a.affil_id FROM resources r, resourcegroupaffil rg, resourceaffil a where 
r.resource_id = a.resource_id and r.resource_id = rg.resource_id and 
r.requester_vis_only = '1’;

I tested the view creation with that code interactively, and it worked.

However when I re-run the import_all.sh script it errors out at the same place 
with the same error (in fact the pasted error code is from running the script 
AFTER I fixed it). The sql isn’t being cached anywhere in the system is it?

I know that I modified the correct file.

It happens even if I drop the database and recreate it.

--
Bruce Johnson
University of Arizona
College of Pharmacy
Information Technology Group

Institutions do not have opinions, merely customs

Re: [EXT]Re: Accessing system information functions

[Johnson, Bruce E - (bjohnson)]

Thank you.

On Sep 25, 2023, at 4:18 AM, Erik Wienhold  wrote:

External Email

On 2023-09-22 17:37 +, Johnson, Bruce E - (bjohnson) wrote:
postgres=# pg_database_size(avi);
ERROR:  syntax error at or near "pg_database_size"
LINE 1: pg_database_size(avi);

Call functions with SELECT, e.g.:

SELECT pg_database_size('avi');

Also, the database name must be a string.

Do I have to add anything to the search_path?

Not for objects in schema pg_catalog which is always searched whether
it's listed in the search path or not.

--
Erik


--
Bruce Johnson
University of Arizona
College of Pharmacy
Information Technology Group

Institutions do not have opinions, merely customs

Accessing system information functions

[Johnson, Bruce E - (bjohnson)]

Environment: Ubuntu 22.04 lts, postgres 15 installed via 
postgres.org repository

I am attempting to use the system information functions here: 
https://www.postgresql.org/docs/15/functions-info.html

I’m logged on as the postgres superuser on the host

Trying any of the functions gives me a syntax error


postgres=# pg_database_size(avi);

ERROR:  syntax error at or near "pg_database_size"

LINE 1: pg_database_size(avi);


Do I have to add anything to the search_path?


postgres=# show search_path;

   search_path

-

 "$user", public

(1 row)



--
Bruce Johnson
University of Arizona
College of Pharmacy
Information Technology Group

Institutions do not have opinions, merely customs

Connection not allowed because of an error 'Not in pg_hba.conf'

[Johnson, Bruce E - (bjohnson)]

I am doing some testing trying to migrate some websites from using Oracle to 
Postgres. (Using Perl DBI and DBD::Pg as the connecting mechanism)

(Server Environment Postgres 15 running on Ubuntu 22.04, client Rocky Linux 
using the supplied PostgreSQL DBI and DBD::Pg packages)

The error I am getting on the client is:


 password authentication failed for user "trav"

connection to server at "dhbpostgres.pharmacy.arizona.edu" (10.128.206.109), 
port 5432 failed: FATAL:  no pg_hba.conf entry for host "10.128.206.109", user 
"trav", database "webdata", no encryption

But I do have an entry that should allow it:


#Internal server mgmt range

hostssl all all 10.128.206.0/23 password

>From the manual (pg 704, 21.1. The pg_hba.conf File):


"An IP address range is specified using standard numeric notation for the 
range's starting address, then a slash (/) and a CIDR mask length. The mask 
length indicates the number of high-order bits of the client IP address that 
must match. Bits to the right of this should be zero in the given IP address. 
There must not be any white space between the IP address, the /, and the CIDR 
mask length.

Typical examples of an IPv4 address range specified this way are 
172.20.143.89/32 for a single host, or 172.20.143.0/24 for a small network, or 
10.6.0.0/16 for a larger one. "

10.128.206.109 is definitely in that range.

The test script DOES work with my desktop running the same software, but I have 
it set in pg_hba.conf as just my systems ip:

 hostssl webdata  trav   nnn.nnn.nnn.nnn/32 password


(Ip address redacted because it is externally accessible)

--
Bruce Johnson
University of Arizona
College of Pharmacy
Information Technology Group

Institutions do not have opinions, merely customs

Granting privileges to a schema to a role

[Johnson, Bruce E - (bjohnson)]

(Background I’m working on migrating an existing set of Oracle schemas with a 
bunch of inter-schema grants to a Postgres 15 system)

I’ve created the database ‘webdata', successfully used ora2pg to migrate one 
schema ‘trav’ to Postgres.

The schema on the oracle side is called trav the owner is webdata, and I 
created the role trav and granted all table rights in the schema to the role

GRANT ALL ON ALL TABLES IN SCHEMA trav TO trav.

When I log into pgsql as trav and run \dp the privileges appear correct but 
trying a simple select fails with ‘permission denied’ error:


psql webdata -U trav

psql (15.4 (Ubuntu 15.4-1.pgdg22.04+1))

Type "help" for help.


webdata=> \dp trav.sectors

 Access privileges

 Schema |  Name   | Type  |Access privileges| Column privileges | 
Policies

+-+---+-+---+--

 trav   | sectors | table | webdata=arwdDxt/webdata+|   |

| |   | trav=arwdDxt/webdata|   |

(1 row)


webdata=> select sectorname, count(worldname) from trav.sectors group by 
sectorname order by sectorname;

ERROR:  permission denied for schema trav

LINE 1: select sectorname, count(worldname) from trav.sectors group ...

What am I missing?


--
Bruce Johnson
University of Arizona
College of Pharmacy
Information Technology Group

Institutions do not have opinions, merely customs

listening on an address other than 'localhost'?

[Johnson, Bruce E - (bjohnson)]

per the documentation to listen for client connections on a given IP address 
you set listen_addresses to a commaspearated list of IP addresses in 
postgresql.conf

That line is commented out in the default conf file, when I uncomment it, and 
add the address, then do sudo systemctl restart postgresql the postgres 
processes are not running afterwards.

root@dhbpostgres:/home/johnson# ps -aux | grep postgres
root1452  0.0  0.0   6608  2364 pts/1S+   19:07   0:00 grep 
--color=auto postgres
root@dhbpostgres:/home/johnson# vi /etc/postgresql/15/main/postgresql.conf 
 apt repo.


--
Bruce Johnson
University of Arizona
College of Pharmacy
Information Technology Group

Institutions do not have opinions, merely customs

Strategy for migrating from Oracle to PG

[Johnson, Bruce E - (bjohnson)]

I have an oracle database with a bunch of schemas that are the data sources for 
bunch of web applications; what used to be called an ‘intranet’.

I’ve installed pg2sql, and it’s working, and what I want to do is pretty much 
copy what I existing now as closely as possible.

I've done ora2pg —project_base /oracle_migrate/ —init_project [schema name]

Properly set the schema username and password in the ./config/ora2pg.conf filer 
each of them

Do I need to create the users in Postgres, or is that done as part of the 
import_all.sh script?

The schemas themselves are not all that complex, mostly just tables, views and 
a handful of procedures and grants.

Installed version of Postgres is v15.

--
Bruce Johnson
University of Arizona
College of Pharmacy
Information Technology Group

Institutions do not have opinions, merely customs