Re: Connection hangs on new created schema

Juan Manuel Cuello wrote: > I have a postgresql database with around 4000 schemas. Each schema has around > the same 65 tables. There are 8 processes connected to the database (it is a > web app). > Each process has only one direct connection to the database (no connection > pool). > > Everythin

Re: Issue with PostgreSQL replication and PITR

Amee Sankhesara - Quipment India wrote: > We have setup replication and PITR on this PostgreSQL server and wal files > for replication and PITR we are storing on the another server which is > shared with primary and secondary server. > > For doing some OS migration activity for the PostgreSQL dat

Re: Hello all, I wanted to install any postgresql database for my development

sanjeev kumar schrieb am 28.03.2018 um 08:01: > Please suggest me any verion of PostgreSQL Database for my development > testing are improving my skills. If you are starting with Postgres the best choice is to use the current version which is 10. If you are using Linux, please follow the instru

Hello all, I wanted to install any postgresql database for my development

Hello all, Please suggest me any verion of PostgreSQL Database for my development testing are improving my skills. -- Regards, -Sanjeeva

Re: pg_dump -Fd -j2 on standby in 9.6.6

On 28 March 2018 07:02:30 CEST, Stefan Petrea wrote: >Hi, > > >I wonder why synchronized snapshots are not supported on standby >servers. > > > If you want to take backups using -Fd on a standby you should pause the reply-process. In this case you don' t need synchronized snapshots. Regards, An

RE: [EXTERNAL]pg_dump -Fd -j2 on standby in 9.6.6

Just wanted to clarify what my question is. I tried this with both pg_dump 9.6.6 and 9.6.8. What I'd like is to use pg_dump -Fd -j2 to generate dumps faster. But I get the error pg_dump: Synchronized snapshots are not supported on standby servers. Run with --no-synchronized-snaps

pg_dump -Fd -j2 on standby in 9.6.6

Hi, I downloaded the code from this github tag https://github.com/postgres/postgres/archive/REL9_6_8.tar.gz I unpacked the code on disk where gdb was looking for it /build/postgresql-9.6-9VrjLn/postgresql-9.6-9.6.8 Then I ran this oneliner which is a summary of my debug process. So, apparently

Re: connection dropped from the backend server

On 03/27/2018 07:21 PM, armand pirvu wrote: On Mar 27, 2018, at 6:49 PM, Tom Lane > wrote: Adrian Klaver > writes: On 03/27/2018 04:07 PM, armand pirvu wrote: As long as the connection stays up yes data gets fine across In pg_stat_

Re: connection dropped from the backend server

Sent from my iPhone > On Mar 27, 2018, at 9:21 PM, armand pirvu wrote: > > >>> On Mar 27, 2018, at 6:49 PM, Tom Lane wrote: >>> >>> Adrian Klaver writes: On 03/27/2018 04:07 PM, armand pirvu wrote: As long as the connection stays up yes data gets fine across In pg_stat_activ

Re: connection dropped from the backend server

> On Mar 27, 2018, at 6:49 PM, Tom Lane wrote: > > Adrian Klaver writes: >> On 03/27/2018 04:07 PM, armand pirvu wrote: >>> As long as the connection stays up yes data gets fine across >>> In pg_stat_activity I see the node ip address where tail -f piped into psql >>> happens > >> So what doe

Re: connection dropped from the backend server

Adrian Klaver writes: > On 03/27/2018 04:07 PM, armand pirvu wrote: >> As long as the connection stays up yes data gets fine across >> In pg_stat_activity I see the node ip address where tail -f piped into psql >> happens > So what does the rest of that record show? In particular for: I wonder

Re: connection dropped from the backend server

On 03/27/2018 04:07 PM, armand pirvu wrote: As long as the connection stays up yes data gets fine across In pg_stat_activity I see the node ip address where tail -f piped into psql happens So what does the rest of that record show? In particular for: state query backend_start and any others

Re: connection dropped from the backend server

As long as the connection stays up yes data gets fine across In pg_stat_activity I see the node ip address where tail -f piped into psql happens Sent from my iPhone > On Mar 27, 2018, at 6:03 PM, Adrian Klaver wrote: > >> On 03/27/2018 03:36 PM, armand pirvu wrote: >> Hi all >> I have a pro

Re: connection dropped from the backend server

On 03/27/2018 03:36 PM, armand pirvu wrote: Hi all I have a process of some data manipulation and ultimate transfer to a postgres database A DML statement gest concoted with the transformed data and pusshed into a named pipe The named pipe is tailed -f in the background like this nohup $SHEL

Re: Postgres Foreign Data Wrapper and DB2 LUW

Hello, not sure that there is a dedicated fdw for DB2 LUW, but you may try one of ODBC_FDW (and maybe JDBC_FDW) as decribed here: https://wiki.postgresql.org/wiki/Foreign_data_wrappers Regards PAscal -- Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html

Re: Troubleshooting a segfault and instance crash

As a follow up, we’ve been able to get the same back trace implicating pg_hint_plan from 2 separate crashes. We were using pg_hint_plan 1.2.2--we reported the issue to pg_hint_plan github. We’ve removed pg_hint_plan and it looks like the system will no longer segfault under the same conditions

connection dropped from the backend server

Hi all I have a process of some data manipulation and ultimate transfer to a postgres database A DML statement gest concoted with the transformed data and pusshed into a named pipe The named pipe is tailed -f in the background like this nohup $SHELL <

Re: ERROR: invalid byte sequence for encoding "UTF8": 0xfc

> On Mar 27, 2018, at 3:47 PM, armand pirvu wrote: > > Folks > > > From another system (non postgres) as data gets changed we collect in the > form like below into a named pipe which is tailed into psql -U csidba -d > repdb -hrephost > > insert into foo (mydata) values ('{"mail_stop": "" ,

Congrats to PostgreSQL sponsor OpenSCG

Community, Major PostgreSQL Sponsor OpenSCG has just been acquired by PostgreSQL sponsor AWS. Congrats to both companies! JD -- Command Prompt, Inc. || http://the.postgres.company/ || @cmdpromptinc *** A fault and talent of mine is to tell it exactly how it is. *** PostgreSQL centered full

Re: Postgres Foreign Data Wrapper and DB2 LUW

I require a $7500 advance. On Tue, Mar 27, 2018, 11:15 AM Rakesh Kumar wrote: > Is there a web page describing how to set up a PG FDW to talk to DB2 LUW > ver 10.5 ? > > > Thanks. > >

Re: Proposition for better performance

> On Mar 27, 2018, at 4:25 PM, Paul Jungwirth > wrote: > > On 03/27/2018 11:42 AM, hmidi slim wrote: >> This is the message that I got: >> Successfully run. Total query runtime: 2 min. >> 1500 rows affected. > > Sorry, I don't think this is enough information to suggest anything. > > --

Connection hangs on new created schema

I have a postgresql database with around 4000 schemas. Each schema has around the same 65 tables. There are 8 processes connected to the database (it is a web app). Each process has only one direct connection to the database (no connection pool). Everything works ok until a new schema (with all it

Re: Proposition for better performance

On 03/27/2018 11:42 AM, hmidi slim wrote: This is the message that I got: Successfully run. Total query runtime: 2 min. 1500 rows affected. Sorry, I don't think this is enough information to suggest anything. -- Paul ~{:-) p...@illuminatedcomputing.com

Postgres Foreign Data Wrapper and DB2 LUW

Is there a web page describing how to set up a PG FDW to talk to DB2 LUW ver 10.5 ? Thanks.

Re: Postgresql 9.3 Server will not start after Ubuntu Upgrade

On 03/27/2018 01:46 PM, Ken Beck wrote: I am working on two systems, one running in Oracle VirtualBox on my laptop, the other in a DigitalOcean droplet. I know on one of them I tried to remove the postgres-9.6, and it must have been my laptop, here is the output from pg_lsclusters: Ver Cluster P

Re: Postgresql 9.3 Server will not start after Ubuntu Upgrade

Ken Beck writes: > I recently upgraded my OS from Ubuntu 14.04 LTS to 16.04 LTS and since, > postgresql refuses to re-start. I am concerned that if I poke around too > much, I may lose my old data. I have consulted various web postings here > and in ubuntu forums, and have not found an obvious s

ERROR: invalid byte sequence for encoding "UTF8": 0xfc

Folks From another system (non postgres) as data gets changed we collect in the form like below into a named pipe which is tailed into psql -U csidba -d repdb -hrephost insert into foo (mydata) values ('{"mail_stop": "" , "city_id": "Garching bei M?nchen"}'); And got a failure ERROR: inval

Re: Postgresql 9.3 Server will not start after Ubuntu Upgrade

I am working on two systems, one running in Oracle VirtualBox on my laptop, the other in a DigitalOcean droplet. I know on one of them I tried to remove the postgres-9.6, and it must have been my laptop, here is the output from pg_lsclusters: Ver Cluster Port Status Owner    Data directory

Re: Postgresql 9.3 Server will not start after Ubuntu Upgrade

OK, there are some logs there, most have old date/time stamps. But there is one with today's time on it, but all it has is week-old data, likely from near the time I did the Ubuntu upgrade. The following entry makes me worry (it is about 2/3 of the way down...) 2018-03-22 10:14:28 MDT LOG:  databa

Re: Check constraints.

On 27/03/18 15:44, Paul Jungwirth wrote: >   SELECT  c.conname, >  ... This just does a variation on select * from information_schema.check_constraints, and has the same issue, that is the the returned value for the constraint is not what I give when I create it - but some 'normalised' vers

Re: Problem with postgreSQL

On 03/27/2018 11:39 AM, Gian mario Caggiu wrote: I downloaded two versions, 9.6.8 and 10.3, but both did not work. I followed the instructions on the website and in the page where I have to choose the version and the operating system I chose the two versions that I have already told you. then

Re: Proposition for better performance

This is the message that I got: Successfully run. Total query runtime: 2 min. 1500 rows affected.

Re: Proposition for better performance

On 03/27/2018 11:14 AM, hmidi slim wrote: Query plan:* Bitmap Heap Scan on product_availabilities  (cost=33728.79..236086.04 rows=878500 width=26) (actual time=2775.058..5792.842 rows=1500 loops=1)   Recheck Cond: (during @> '[2018-02-01,2018-04-01)'::daterange)   Heap Blocks: exact=31040

Re: Proposition for better performance

(Including the list) On 03/27/2018 10:49 AM, hmidi slim wrote: Sorry I didn't copy it very well: create index idx on availability using gist(during); and during = daterange(start_date,end_date) And the query plan used was seq scan not index scan. It sounds like there must be some importa

Re: Postgresql 9.3 Server will not start after Ubuntu Upgrade

On 03/27/2018 11:00 AM, Ken Beck wrote: I recently upgraded my OS from Ubuntu 14.04 LTS to 16.04 LTS and since, postgresql refuses to re-start. I am concerned that if I poke around too much, I may lose my old data. I have consulted various web postings here and in ubuntu forums, and have not foun

Re: Proposition for better performance

I update the example: *create table product_availabilities(product_id integer, start_date date, end_date date); insert into product_availabilities(product_id, start_date, end_date) select a, '2018-01-01', '2018-05-03' from generate_series(1,1500) as aalter ta

Re: Postgresql 9.3 Server will not start after Ubuntu Upgrade

Il 27/03/2018 20:00, Ken Beck ha scritto: And, looking for log files, I find none. Have you looked in /var/log/postgresql/ ?

Re: Postgresql 9.3 Server will not start after Ubuntu Upgrade

On 03/27/2018 11:00 AM, Ken Beck wrote: And, looking for log files, I find none. Nothing in /var/log/postgresql or /var/lib/postgresql/9.3/main/pg_log? JD -- Command Prompt, Inc. || http://the.postgres.company/ || @cmdpromptinc *** A fault and talent of mine is to tell it exactly how it is.

Postgresql 9.3 Server will not start after Ubuntu Upgrade

I recently upgraded my OS from Ubuntu 14.04 LTS to 16.04 LTS and since, postgresql refuses to re-start. I am concerned that if I poke around too much, I may lose my old data. I have consulted various web postings here and in ubuntu forums, and have not found an obvious solution. But please excuse m

Re: Proposition for better performance

On 03/27/2018 10:04 AM, hmidi slim wrote: the query that I used to fetch products was: select * from availability where ('27-03-2018' between start_date and end_date) and ('31-03-2018' between start_date and end_date); I added another column named during of type daterange and I created a gist i

Re: Proposition for better performance

the query that I used to fetch products was: select * from availability where ('27-03-2018' between start_date and end_date) and ('31-03-2018' between start_date and end_date); I added another column named during of type daterange and I created a gist index : create index idx on availability(durin

Re: Problem with postgreSQL

On 03/27/2018 07:18 AM, Gian mario Caggiu wrote: Please reply to list also. Ccing list. I downloaded the first time 9.6 version, and when i saw that it doesn’t work i downloaded an other version, 10.3, but Also this doesn’t work. All downloads from http://www.postgresql.org . And i install it

Re: Proposition for better performance

On 03/27/2018 07:42 AM, hmidi slim wrote: Hi, I'm trying to create an availability table for existing products. I'm fetching the suitable schema to design in order to get good performance when I fetch products in a table contains millions of rows. I think to make a schema like this: *create ta

Re: Using Lateral

On 03/27/2018 03:22 AM, Johann Spies wrote: In the past I could use this in a query: SELECT DISTINCT ut, CASE WHEN xpath_exists ('//t:address_spec/t:country/text()', q.address_spec, p.ns) THEN unnest (xpath ('//t:

Re: Autonomous transaction, background worker

I try to setup a logging facility and want it to do its work asynchronously in autonomous transactions. I have read http://blog.dalibo.com/2016/08/19/Autonoumous_transactions_support_in_PostgreSQL.html and chapter 47 of the documentation believing those articles are about the same. However, pg_

Re: Check constraints.

On 03/27/2018 04:23 AM, Steve Rogerson wrote: I am trying to add/change a constraint programmatically, but not if it already exists, and is the same as before. ... Is there a way of getting the "normalised" version of constraint so decide if I need to update the constraint if one already exists?

Proposition for better performance

Hi, I'm trying to create an availability table for existing products. I'm fetching the suitable schema to design in order to get good performance when I fetch products in a table contains millions of rows. I think to make a schema like this: *create table availability (product_id integer, product_n

logical decoder lsn order between transactions

Hi, We have written a decoder plugin to use streaming replication in an aggregation framework and we write the changes in chunks using OutputPluginWriter from pg_decode_change. In order to ensure we don't process a message twice we add the lsn of each message to our aggregated value and check a

Re: Must re-connect to see tables

Blake McBride wrote: > data.sql is an untouched dump of a database from PostgreSQL 9.5.12. > > The import seems to have worked. I just need another \c after the \i. An effect of reconnecting is to reset the search_path to what it was before including your dump file. You're most certainl

Re: Must re-connect to see tables

On Tue, Mar 27, 2018 at 6:48 AM, rob stone wrote: > Hello Blake, > > On Tue, 2018-03-27 at 06:23 -0500, Blake McBride wrote: > > I should also mention that I am using PostgreSQL 9.3.22 on a 64 bit > > Linux box. > > > > On Tue, Mar 27, 2018 at 6:22 AM, Blake McBride > > wrote: > > > Hi, > > > >

Re: Must re-connect to see tables

Hello Blake, On Tue, 2018-03-27 at 06:23 -0500, Blake McBride wrote: > I should also mention that I am using PostgreSQL 9.3.22 on a 64 bit > Linux box. > > On Tue, Mar 27, 2018 at 6:22 AM, Blake McBride > wrote: > > Hi, > > > > I have been using PostgreSQL for many years but all of a sudden a >

Re: Must re-connect to see tables

If it worked in 9.3.22 and now it's failed in 9.3.22, then  I'd look to see if something has changed in data.sql. On 03/27/2018 06:22 AM, Blake McBride wrote: Hi, I have been using PostgreSQL for many years but all of a sudden a db load script I've been using no longer works.  What id does (e

Check constraints.

williamI am trying to add/change a constraint programmatically, but not if it already exists, and is the same as before. I can so something like (may not be precise ...) select check_clause from information_schema.check_constraints where constraint_name = 'my-name' but this returns that clause

Re: Must re-connect to see tables

I should also mention that I am using PostgreSQL 9.3.22 on a 64 bit Linux box. On Tue, Mar 27, 2018 at 6:22 AM, Blake McBride wrote: > Hi, > > I have been using PostgreSQL for many years but all of a sudden a db load > script I've been using no longer works. What id does (essentially) is: > > p

Must re-connect to see tables

Hi, I have been using PostgreSQL for many years but all of a sudden a db load script I've been using no longer works. What id does (essentially) is: psql postgres postgres drop database test; create database test; \c test \i data.sql \dt \q I tested this with a small data.sql and it worked fin

Using Lateral

In the past I could use this in a query: SELECT DISTINCT ut, CASE WHEN xpath_exists ('//t:address_spec/t:country/text()', q.address_spec, p.ns) THEN unnest (xpath ('//t:address_spec/t:country/text()', q

Issue with PostgreSQL replication and PITR

[Quipment Logo] Hello, We have a windows(2016) setup for PostgreSQL(Version 9.6.5). We have setup replication and PITR on this PostgreSQL server and wal files for replication and PITR we are storing on the another server which is shared with primary and secondary server. For doing some OS mig

Re: Can't get policy to work correctly

Well I seems I got it working. Still not sure what I did wrong. I finally got it working by moving the sub select into a view and only use the view in the policy: (u): ((hs_group_id IS NULL) OR (hs_group_id IN ( SELECT allowed_hs_groups.hs_group_id FROM allowed_hs_groups))) The allowed_hs

Can't get policy to work correctly

Hi list! I am a long postgres user but only since a short time I am using the more advanced stuff. And now I use the row level security I run into a problem. I use postgres 9.5.12. I have multiple users; postgres, root and ivo. I have a table called person. It contains multiple rows that should b