PostgreSQL

06 Nov 2024 17:39

Everybody can help me with the "database cluster is failed" problem

PostgreSQL

06 Nov 2024 15:09

Pgadmin4, livecompare and several other postgres desktop client have this type of functionality

PostgreSQL

06 Nov 2024 15:07

How to compare the two databases in the same cluster? I have scenario where cluster having 2 databases as “test” and “test_old” so want to compare the tables,functions and indexes of both databases

PostgreSQL

06 Nov 2024 15:01

Standard copy command have added a parameter that allow you to get rows that rise an error during the execution of the copy

PostgreSQL

06 Nov 2024 14:51

Value Separator=\t
Include Header=Line true
Quote Character="
Escape Character=\p
Comment Marker=No value set
Null String=No value set
Trim Fields=true
Quote Mode=Quote All Values
Record Separator=\n
Include Trailing Delimiter=true

copy ${table_name}
from 's3://yyy-dwh/load2redshift/2load/${filename}'
REGION 'us-east-1' iam_role 'arn:aws:iam::45645645:role/redshift-s3'
FORMAT AS CSV  TRUNCATECOLUMNS IGNOREHEADER 1 DELIMITER '\t'  STATUPDATE OFF QUOTE '"' ;

PostgreSQL

06 Nov 2024 14:47

I am using Nifi with "CSVRecordSetWriter" in order to create the CSV. but I keep on failing.

PostgreSQL

06 Nov 2024 14:46

Have a valid CSV file

PostgreSQL

06 Nov 2024 14:34

Ah, i always assumed they used the first one. Today i learned

PostgreSQL

06 Nov 2024 14:26

You cannot use smallint/int if the result does not fit

PostgreSQL

06 Nov 2024 14:21

We migrated code from firebird to postgres.. Around 300 procedures are using small int

PostgreSQL

06 Nov 2024 07:39

[oracle@lsp-oratl01 ora2pg]$ export ORACLE_HOME=/u1/oracle/app/oracle/product/19.0.0/client_2
[oracle@lsp-oratl01 ora2pg]$ export LD_LIBRARY_PATH=/u1/oracle/app/oracle/product/19.0.0/client_2/lib
[oracle@lsp-oratl01 ora2pg]$ ora2pg -t SHOW_VERSION -c ora2pg.conf
FATAL: 2000000000 ... Failed to allocate OCIEnv
Aborting export...

PostgreSQL

06 Nov 2024 06:49

please suggest on this

PostgreSQL

06 Nov 2024 04:41

Has anyone tried multi-master via logical replication in v17? Is the with origin option mature enough for heavy-load transactions?

PostgreSQL

05 Nov 2024 21:46

issue fixed by
cd /usr/lib64/

ln -s /usr/lib64/libnsl.so.1 libnsl.so

PostgreSQL

05 Nov 2024 21:26

You can follow the installation process as this docker image https://github.com/Bluestep-Systems/ora2pg-docker/blob/main/Dockerfile

PostgreSQL

06 Nov 2024 15:22

You can do pg dump without data and comapre the files

PostgreSQL

06 Nov 2024 15:08

You just want to compare the schema?

PostgreSQL

06 Nov 2024 15:01

https://www.depesz.com/2024/02/07/waiting-for-postgresql-17-add-new-copy-option-save_error_to-rename-copy-option-from-save_error_to-to-on_error/

PostgreSQL

06 Nov 2024 14:59

This the copy command of redshift

PostgreSQL

06 Nov 2024 14:48

Apparently, it's just that the operator fitting best without a CAST is picked:

  Schema   | Name | Left arg type | Right arg type | Result type | Description 
------------+------+---------------+----------------+-------------+-------------
 pg_catalog | *    | bigint        | bigint         | bigint      | multiply
 pg_catalog | *    | bigint        | integer        | bigint      | multiply
 pg_catalog | *    | bigint        | money          | money       | multiply
 pg_catalog | *    | bigint        | smallint       | bigint      | multiply
 pg_catalog | *    | integer       | bigint         | bigint      | multiply
 pg_catalog | *    | integer       | integer        | integer     | multiply
 pg_catalog | *    | integer       | money          | money       | multiply
 pg_catalog | *    | integer       | smallint       | integer     | multiply
 pg_catalog | *    | smallint      | bigint         | bigint      | multiply
 pg_catalog | *    | smallint      | integer        | integer     | multiply
 pg_catalog | *    | smallint      | money          | money       | multiply
 pg_catalog | *    | smallint      | smallint       | smallint    | multiply

PostgreSQL

06 Nov 2024 14:47

Thankfully, the postgresql parser will notify you of such errors, so that you don't end up importing corrupted data. Set the QUOTE correct and it should import right away assuming it's not invalid

PostgreSQL

06 Nov 2024 14:45

What is the best way to COPY csv file without keep failing because delimiter sometimes is within the string? or there's line break and etc?

PostgreSQL

06 Nov 2024 14:33

A smallint is 16 bit IIRC, an INT is 32 bit.

The multiplication is done *before* the assignment to "C", and multiplications are always using the bigger of the the two operand types.
Like:

bluth=> select 5000::smallint * 1000::smallint;
ERROR:  smallint out of range
bluth=> select 5000::smallint * 1000::int;
 ?column? 
----------
  5000000
(1 Zeile)

PostgreSQL

06 Nov 2024 14:23

Yes, you need to cast the first one to bigint, so that the whole operation is made with bigint

PostgreSQL

06 Nov 2024 14:20

When i multiple two small integer, getting this error

Smallint out of range

Example

A smallint:=5000
B smallint :=1000
C integer;

C:=A*B;

PostgreSQL

06 Nov 2024 07:15

[root@lsp-oratl01 ora2pg]# export ORACLE_HOME=/u1/oracle/app/oracle/product/19.0.0/client_2
[root@lsp-oratl01 ora2pg]# export PATH=$PATH:$ORACLE_HOME/bin
[root@lsp-oratl01 ora2pg]# export LD_LIBRARY_PATH=$ORACLE_HOME/lib
[root@lsp-oratl01 ora2pg]# ora2pg -t SHOW_VERSION -c ora2pg.conf
FATAL: 2000000000 ... Failed to allocate OCIEnv
Aborting export...
[root@lsp-oratl01 ora2pg]# pwd
/etc/ora2pg
[root@lsp-oratl01 ora2pg]# su - postgres
Last login: Tue Nov 5 20:49:07 UTC 2024 on pts/3
[postgres@lsp-oratl01 ~]$ cd /etc/ora2pg
[postgres@lsp-oratl01 ora2pg]$ export ORACLE_HOME=/u1/oracle/app/oracle/product/19.0.0/client_2
[postgres@lsp-oratl01 ora2pg]$ export PATH=$PATH:$ORACLE_HOME/bin
[postgres@lsp-oratl01 ora2pg]$ export LD_LIBRARY_PATH=$ORACLE_HOME/lib
[postgres@lsp-oratl01 ora2pg]$ ora2pg -t SHOW_VERSION -c ora2pg.conf
install_driver(Oracle) failed: Can't load '/usr/local/lib64/perl5/auto/DBD/Oracle/Oracle.so' for module DBD::Oracle: libclntsh.so.19.1: cannot open shared object file: No such file or directory at /usr/lib64/perl5/DynaLoader.pm line 193.
at (eval 14) line 3.
Compilation failed in require at (eval 14) line 3.
Perhaps a required shared library or dll isn't installed where expected
at /usr/local/share/perl5/Ora2Pg/Oracle.pm line 144.
[postgres@lsp-oratl01 ora2pg]$

PostgreSQL

06 Nov 2024 06:48

In file included from Pg.xs:14:
Pg.h:35:10: fatal error: libpq-fe.h: No such file or directory
#include "libpq-fe.h"
^~~~~~~~~~~~
compilation terminated.
make: *** [Makefile:357: Pg.o] Error 1

PostgreSQL

05 Nov 2024 21:46

[root@lsp-oratl01 software]# cd DBD-Pg-3.18.0
[root@lsp-oratl01 DBD-Pg-3.18.0]# perl Makefile.PL
Configuring DBD::Pg 3.18.0
Path to pg_config? /usr/pgsql-16/bin
sh: /usr/pgsql-16/bin: Is a directory
Enter a valid PostgreSQL postgres major version number 16
Enter a valid PostgreSQL postgres minor version number 16
Enter a valid PostgreSQL postgres patch version number 16
sh: /usr/pgsql-16/bin: Is a directory
sh: /usr/pgsql-16/bin: Is a directory
Enter a valid PostgreSQL postgres bin dir
getting struck here

PostgreSQL

05 Nov 2024 21:29

Find if you have installed libnsl library and if you have in your system, include it in ldlibrarypath variable

PostgreSQL

05 Nov 2024 21:20

while installing Install Oracle database driver for the DBI module(DBD-Oracle )