Barman issue

[Rijo Roy]

Hello Experts,
I have a server rhel 6.9 installed with barman 2.3 which is configured to take 
backup from Postgresql 10 installed in a remote host and the same is working.
I am trying to do a poc on streaming backup and streaming wal method by making 
use of the pg_receivewal. I have already created a user with replication 
privilege the same is added in pg_hba. conf. And I am able to connect remotely 
using the user created.
But when I am running barman check pg
pg is the name given to the remote dB host. I am getting an error like this
EXCEPTION: LooseVersion instance has no attribute 'version'
Below I am pasting the conf files used:
[pg] 
conninfo
streaming_conninfo
streaming_archiver = on
slot_name = barman
backup_method = postgres 
path_prefix = Postgresql bin path
basebackups_directory
errors_directory
incoming_wals_directory
streaming_wals_directory

---—---
I have already created the replication slots and started the pg_receivexlog 
remotely from the batman server. Was are streaming and is coming to the 
incoming_wals_directory 
Please note that there are 2 more servers configured with barman in this backup 
hosts which is working fine.. The o my difference theyvhave is that they are 
configured with backup_method = rsync.

I wanted to make use of pg_receivexlog instead of rsync. Hence this poc..

Please help. 


Thanks,

Roy

Sent from Yahoo Mail on Android

Does postgreSQL community edition supports data distribution across nodes

[deepikags]

Hi,

Does postgreSQL community edition supports data distribution across
nodes(distributed architecture) or do we need to buy licence for the same ?


Regards,
Deepika



--
Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html

Re: Does postgreSQL community edition supports data distribution across nodes

[Fabio Pardi]

Hi,

I think you are looking for:

https://www.postgres-xl.org/

regards,

fabio pardi

On 05/10/18 11:54, deepikags wrote:
> Hi,
>
> Does postgreSQL community edition supports data distribution across
> nodes(distributed architecture) or do we need to buy licence for the same ?
>
>
> Regards,
> Deepika
>
>
>
> --
> Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html
>

Re: Does postgreSQL community edition supports data distribution across nodes

[James Keener]

Also
https://wiki.postgresql.org/wiki/Replication,_Clustering,_and_Connection_Pooling
and https://www.postgresql.org/docs/10/static/logical-replication.html for
other options as well.

On Fri, Oct 5, 2018 at 7:35 AM Fabio Pardi  wrote:

> Hi,
>
> I think you are looking for:
>
> https://www.postgres-xl.org/
>
> regards,
>
> fabio pardi
>
> On 05/10/18 11:54, deepikags wrote:
>
> Hi,
>
> Does postgreSQL community edition supports data distribution across
> nodes(distributed architecture) or do we need to buy licence for the same ?
>
>
> Regards,
> Deepika
>
>
>
> --
> Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html
>
>
>

Prevent locked state (row lock + alter table)

[Durumdara]

Dear Members!

We have a big database somewhere with more than 150 active connection.

Sometimes we experienced a "lock" situation, the client's and programs are
halted on a point.

We investigated a little, and we recognized that this problem seems to be
appears when we modify a table (alter) on high usage (morning-noon).

This table called "art".

For not modify by paralell users, we use row lock in transaction.

For example:

try
StartTrans;
try
  set lock timeout to 30 sec
  select  * from art for update where id = ?
  Modifications
  Commit
catch error
   Rollback
finally
   set lock timeout to default

As I think this lock interferes with the alter table on high usage. On
10-20 live connection (night) it didn't happen.

The alter example:

alter table art add blabla int;

As we experienced the whole system stopped on queries (they wait).

>From previously opened PGAdmin I can exec a Query to other table, so PG is
working.

Please help me a little:

Do you have any experince on same problem?

Which session (local) timeout parameter I need to set and limit to lower
for avoid these problem?

How can I detect the conflict (can I exec a query which show me, what
happens) when the problem is on?

Thank you for your any help!

dd

COPY from a remote machine in Datastage

[Ravi Krishna]

We are doing a POC of using Datastage with PG using ODBC.  

Problem to solve:  How to load a large CSV file using COPY command.  The file 
is on the client machine.

A typical SQL syntax of a copy coming from a remote machine  COPY TABLE FROM 
STDIN WITH CSV HEADER

Question is, how to make the contents of the file available as STDIN in a SQL.  
It is easy in a shell.

Re: COPY from a remote machine in Datastage

[Josef Šimánek]

Hello, if you need to use COPY command from remote machine and you use some
libpq bindings (aka ruby pg gem for example), you can use functions
associated with COPY command (
https://www.postgresql.org/docs/10/static/libpq-copy.html). They should be
provided by bindings of postgres library you're using.

If you can share more info, at least how do you access postgres (via Ruby
gem for example or from client's console), I can try to be more descriptive.

Josef

pá 5. 10. 2018 v 16:06 odesílatel Ravi Krishna  napsal:

>
> We are doing a POC of using Datastage with PG using ODBC.
>
> Problem to solve:  How to load a large CSV file using COPY command.  The
> file is on the client machine.
>
> A typical SQL syntax of a copy coming from a remote machine  COPY TABLE
> FROM STDIN WITH CSV HEADER
>
> Question is, how to make the contents of the file available as STDIN in a
> SQL.  It is easy in a shell.
>

Re: COPY from a remote machine in Datastage

[Ravi Krishna]

> 
> Hello, if you need to use COPY command from remote machine and you use some 
> libpq bindings (aka ruby pg gem for example), you can use functions 
> associated with COPY command 
> (https://www.postgresql.org/docs/10/static/libpq-copy.html 
> ). They should be 
> provided by bindings of postgres library you're using.
> 
> If you can share more info, at least how do you access postgres (via Ruby gem 
> for example or from client's console), I can try to be more descriptive.

We not writing any Ruby/Python code.  We are using Datastage. Datastage has in 
built features for most of the stuff. For example to bulk load data from csv 
files
into Oracle/DB2 etc, it has a BULK loader feature. However DS has no support 
for PG directly and we are piggy backing on ODBC where there is no BULK loader.

The only recourse for us is to type in SQL as DS allows user code.

Re: COPY from a remote machine in Datastage

[Josef Šimánek]

Can you copy your csv to postgres server?

pá 5. 10. 2018 v 16:19 odesílatel Ravi Krishna  napsal:

>
> Hello, if you need to use COPY command from remote machine and you use
> some libpq bindings (aka ruby pg gem for example), you can use functions
> associated with COPY command (
> https://www.postgresql.org/docs/10/static/libpq-copy.html). They should
> be provided by bindings of postgres library you're using.
>
> If you can share more info, at least how do you access postgres (via Ruby
> gem for example or from client's console), I can try to be more descriptive.
>
>
> We not writing any Ruby/Python code.  We are using Datastage. Datastage
> has in built features for most of the stuff. For example to bulk load data
> from csv files
> into Oracle/DB2 etc, it has a BULK loader feature. However DS has no
> support for PG directly and we are piggy backing on ODBC where there is no
> BULK loader.
>
> The only recourse for us is to type in SQL as DS allows user code.
>
>
>

Re: COPY from a remote machine in Datastage

[Ron]

On 10/05/2018 09:18 AM, Ravi Krishna wrote:


Hello, if you need to use COPY command from remote machine and you use 
some libpq bindings (aka ruby pg gem for example), you can use functions 
associated with COPY command 
(https://www.postgresql.org/docs/10/static/libpq-copy.html). They should 
be provided by bindings of postgres library you're using.


If you can share more info, at least how do you access postgres (via Ruby 
gem for example or from client's console), I can try to be more descriptive.


We not writing any Ruby/Python code.  We are using Datastage. Datastage 
has in built features for most of the stuff. For example to bulk load data 
from csv files
into Oracle/DB2 etc, it has a BULK loader feature. However DS has no 
support for PG directly and we are piggy backing on ODBC where there is no 
BULK loader.


The only recourse for us is to type in SQL as DS allows user code.


Can you install the postgres client software (psql) on the client machine 
and then have Datastage spawn "psql -c 'COPY ...'"?


--
Angular momentum makes the world go 'round.

Re: COPY from a remote machine in Datastage

[Ravi Krishna]

> 
> Can you install the postgres client software (psql) on the client machine and 
> then have Datastage spawn "psql -c 'COPY ...'"?

That is already an option for us :-)

Re: COPY from a remote machine in Datastage

[Edmundo Robles]

if you have ssh access to the client, you can do :
ssh  user@client_host "cat  /path_to/large_file.csv" | psql  -d database


On Fri, Oct 5, 2018 at 9:06 AM Ravi Krishna  wrote:

>
> We are doing a POC of using Datastage with PG using ODBC.
>
> Problem to solve:  How to load a large CSV file using COPY command.  The
> file is on the client machine.
>
> A typical SQL syntax of a copy coming from a remote machine  COPY TABLE
> FROM STDIN WITH CSV HEADER
>
> Question is, how to make the contents of the file available as STDIN in a
> SQL.  It is easy in a shell.
>


--

Regarding varchar max length in postgres

[Durgamahesh Manne]

Hi

please let me know the max length of varchar  & text in postgres



Regards

Durgamahesh Manne

Re: Regarding varchar max length in postgres

[Adrian Klaver]

On 10/5/18 8:18 AM, Durgamahesh Manne wrote:

Hi

please let me know the max length of varchar  & text in postgres


https://www.postgresql.org/docs/10/static/datatype-character.html




Regards

Durgamahesh Manne



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

Re: survey: pg_stat_statements total_time and entry deallocation

[legrand legrand]

Hello,

What about adding a log message for each entry_dealloc() execution? 
it could be usefull to start thinking increasing pg_stat_statements.max.

is there any rule regarding the acceptable max value ? I'm playing in test
with a 20 000 value without any problem, could it extendend to 100 000 ?

In a system with pressure on numbers of pgss lines, and regular
entry_dealloc() executions, low frequency  entries are evicted firsts, and
this should still be the same for new small queries with usage based on
total_time. 

Maybe there is a third way, that would be to evict queries based on the
"oldest modification time" ... 
This would garantee that latest queries would be kept long enough to be
collected by aggregation tools.

Regards
PAscal



--
Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html