Re: Problem creating a database

Do you have adequate disk space left on your array? cheers Ben On 15 October 2018 at 17:46, Joshua White wrote: > Hi all, > > I'm hoping someone can point me in the right direction. I've got a > PostgreSQL 10 server instance on CentOS 6, which I set up and manage. I > have full admin rights

Re: Saving view turns SELECT * into field list

Actually, it's super useful, because if someone adds a salaries column to your staff table, it doesn't automatically appear on the front page of your corporate website... :) Made up example, but if you presume that data security is an important part of data management, it is a livesaver. To get

Re: Filtering before join with date_trunc()

Thanks all for the replies. Tom Lane wrote: > You're expecting too much. That often seems to be the case. > I think you're also expecting the system to deduce that it can apply an > inequality on one join column to the other one. It doesn't; only equality > constraints have any sort of

Re: FATAL: terminating connection because protocol synchronization was lost

On 10/15/18 8:10 AM, Shrikant Bhende wrote: Hi Adrain, Its running on the local machine itself. I googled this around but most of the blog says that stop supporting the older version, also I took pg_dumpall from EDB PPAS cluster using PostgreSQL binaries, if that might have caused anything.

Re: Saving view turns SELECT * into field list

Ben Uphoff writes: > Why, when I save a simple view like: > SELECT * FROM a_table > …does PostgreSQL turn the * into a field list like: > SELECT field1, field2, field3, field4 FROM a_table > ? Because the SQL standard says we should. There's explicit text in there to the effect that adding

Re: Saving view turns SELECT * into field list

On Monday, October 15, 2018, Ben Uphoff wrote: > > Why, when I save a simple view like: > > > > SELECT * FROM a_table > > > > …does PostgreSQL turn the * into a field list like: > > > > SELECT field1, field2, field3, field4 FROM a_table > > > Yes, deeply nesting views is a maintenance concern.

Saving view turns SELECT * into field list

Hey team – I’m sure this has come up, but my search engine skills couldn’t find an explanation: Why, when I save a simple view like: SELECT * FROM a_table …does PostgreSQL turn the * into a field list like: SELECT field1, field2, field3, field4 FROM a_table ? This is super-frustrating, as

Re: Setting up continuous archiving

On 10/15/18 5:09 PM, Benoit Lobréau wrote: By the way, do/can they both use streaming to receive WAL records? Or streaming is only for standby servers. For backups you have only file-based log shipping? barman supports streaming but it's not as magical as one might think. See

Re: Filtering before join with date_trunc()

"Phil Endecott" writes: > ... > For each of these tables I have a view which rounds the timestamp > to the nearest minute, and ensures there is only one row per minute: > SELECT date_trunc('minute'::text, tbl."time") AS "time", > max(tbl.pressure) AS pressure >FROM tbl > GROUP BY

Re: Filtering before join with date_trunc()

On 10/15/18 8:57 AM, Phil Endecott wrote: Dear Experts, I have a few tables with "raw" timestamsps like this: +---+--+ | time | pressure | +---+--+ | 2018-09-14 00:00:07.148378+00 | 1007.52 | |

Re: Filtering before join with date_trunc()

Hi Phil: On Mon, Oct 15, 2018 at 5:57 PM, Phil Endecott wrote: ... > For each of these tables I have a view which rounds the timestamp > to the nearest minute, and ensures there is only one row per minute: ... > I then join these tables on the rounded time: > Now I'd like to find the values

Re: Regarding varchar max length in postgres

On 10/15/18 8:56 AM, Durgamahesh Manne wrote:  I request you all community members to provide built in bdr v3 version replication for public as multimaster replication is on high priority against other dbms BDR v3 is third party extension from 2ndQuadrant, it is not community code.

Re: Regarding varchar max length in postgres

On 10/15/18 8:56 AM, Durgamahesh Manne wrote: On Mon, Oct 15, 2018 at 9:07 PM David G. Johnston mailto:david.g.johns...@gmail.com>> wrote: On Mon, Oct 15, 2018 at 8:24 AM Durgamahesh Manne mailto:maheshpostgr...@gmail.com>> wrote: So i need unlimited length data type for

Re: Setting up continuous archiving

By the way, do/can they both use streaming to receive WAL records? Or > streaming is only for standby servers. For backups you have only > file-based log shipping? > barman supports streaming but it's not as magical as one might think. See pgbarman's documentation for how to manager .partial

Filtering before join with date_trunc()

Dear Experts, I have a few tables with "raw" timestamsps like this: +---+--+ | time | pressure | +---+--+ | 2018-09-14 00:00:07.148378+00 | 1007.52 | | 2018-09-14 00:10:07.147506+00 | 1007.43 | |

Re: Regarding varchar max length in postgres

On Mon, Oct 15, 2018 at 9:07 PM David G. Johnston < david.g.johns...@gmail.com> wrote: > On Mon, Oct 15, 2018 at 8:24 AM Durgamahesh Manne < > maheshpostgr...@gmail.com> wrote: > >> So i need unlimited length data type for required column of the table for >> storing the large values >> is there

Re: Regarding varchar max length in postgres

On Mon, Oct 15, 2018 at 8:24 AM Durgamahesh Manne wrote: > So i need unlimited length data type for required column of the table for > storing the large values > is there any issue to use unlimited length datatype text for the > required column of the table instead of using varchar ? >

Re: Setting up continuous archiving

> I am not sure what you call discrete / continuous. >> pgBackRest doesn't seem to allow the latter: recovery to any point in >> time, only to some discrete moments. Correct me if I'm wrong. > > > Are you talking about PITR ? Yes. I had the impression, that with pgBackRest you do backups

Re: Regarding varchar max length in postgres

On Mon, Oct 15, 2018 at 7:54 PM Tom Lane wrote: > Durgamahesh Manne writes: > >>> If character varying is used without length specifier, the type > >>> accepts strings of any size > >>> but varchar does not accept more than this 10485760 value > > You're confusing the size of string that can be

Re: FATAL: terminating connection because protocol synchronization was lost

Hi Adrain, Its running on the local machine itself. I googled this around but most of the blog says that stop supporting the older version, also I took pg_dumpall from EDB PPAS cluster using PostgreSQL binaries, if that might have caused anything. Thanks. On Mon, Oct 15, 2018 at 8:36 PM Adrian

Re: FATAL: terminating connection because protocol synchronization was lost

On 10/15/18 8:04 AM, Shrikant Bhende wrote: Hi Adrian, There is no explicit client its just simple restore using psql with all default settings required. psql is the client. Where is it running relative to the server locally or remote? On Mon, Oct 15, 2018 at 6:50 PM Adrian Klaver

Re: FATAL: terminating connection because protocol synchronization was lost

Hi Adrian, There is no explicit client its just simple restore using psql with all default settings required. On Mon, Oct 15, 2018 at 6:50 PM Adrian Klaver wrote: > On 10/15/18 5:36 AM, Shrikant Bhende wrote: > > Hi Team, > > > > I am trying to restore the backup into postgresql 9.6 and during

Re: FATAL: terminating connection because protocol synchronization was lost

Shrikant Bhende writes: > I am trying to restore the backup into postgresql 9.6 and during restore > copy command fails with below error, > 2018-10-12 06:21:40 UTC [40407]: [28-1] > application=psql.bin,user=postgres,db=cloudLOG: could not send data to > client: Broken pipe This says the

Re: Regarding varchar max length in postgres

Durgamahesh Manne writes: >>> If character varying is used without length specifier, the type >>> accepts strings of any size >>> but varchar does not accept more than this 10485760 value You're confusing the size of string that can be stored with the largest value accepted for "n" in

Re: Setting up continuous archiving

Hi, I am not sure what you call discrete / continuous. pgbackrest allows backups of different kinds: full, incremental and differential. It keeps the wals necessary to recover since the oldest backup until the current time. The retention is expressed in number of full backups. You can also

Re: FATAL: terminating connection because protocol synchronization was lost

On 10/15/18 5:36 AM, Shrikant Bhende wrote: Hi Team, I am trying to restore the backup into postgresql 9.6 and during restore copy command fails with below error, 2018-10-12 06:21:40 UTC [40407]: [28-1] application=psql.bin,user=postgres,db=cloudLOG:  could not send data to client: Broken

Re: Enabling autovacuum per table

On 10/15/18 1:01 AM, Rijo Roy wrote: Hello Experts, Is there any possibility for autovacuum to work on a user table if we set Alter table sometable set (autovacuum_enabled = true) ; even if the parameter autovacuum = off in Postgresql.conf I am using Postgresql 10 on Linux 6.9. According to

New tablespace: just an advice

Hi everyone! My space on my Debian 8 DB server is running a bit low (10% left of a 2TB disk), so, since it's not possible to have a primary MBR disk with size > 2 TB, I decided to create another disk and map it on the server, creating another tablespace on it and moving databases aross disks

FATAL: terminating connection because protocol synchronization was lost

Hi Team, I am trying to restore the backup into postgresql 9.6 and during restore copy command fails with below error, 2018-10-12 06:21:40 UTC [40407]: [28-1] application=psql.bin,user=postgres,db=cloudLOG: could not send data to client: Broken pipe 2018-10-12 06:21:40 UTC [40407]: [29-1]

Re: Setting up continuous archiving

Hi, Thanks for your replies. By the way, I'm now running PostgreSQL 10 :) My idea was to start with continuous archiving, then start a log-shipping standby, then make it use streaming replication. Since I'm kind of overwhelmed with information, options to be considered. Anyways, I'm now trying

Re: Enabling autovacuum per table

Yeah when age(relfrozenxid) goes beyond the limit Postgresql will invoke the autovacuum session to avoid a wraparound issue.. But here that's not the case..  Sent from Yahoo Mail on Android On Mon, 15 Oct 2018 at 2:58 pm, Arthur Zakirov wrote: On 10/15/18 11:01 AM, Rijo Roy wrote: >

Re: Regarding varchar max length in postgres

On Mon, Oct 15, 2018 at 3:11 PM Thomas Kellerer wrote: > Durgamahesh Manne schrieb am 15.10.2018 um 11:18: > > was there any specific reason that you have given max length for varchar > is limited to 10485760 value? > > > > why you have not given max length for varchar is unlimited like text >

Re: Problem creating a database

Joshua White wrote: > I'm hoping someone can point me in the right direction. I've got a PostgreSQL > 10 server > instance on CentOS 6, which I set up and manage. I have full admin rights on > this machine, > so I can access logs, etc. > > Recently I attempted to create a new database in this

Problem creating a database

Hi all, I'm hoping someone can point me in the right direction. I've got a PostgreSQL 10 server instance on CentOS 6, which I set up and manage. I have full admin rights on this machine, so I can access logs, etc. Recently I attempted to create a new database in this cluster. The command

Re: Regarding varchar max length in postgres

Durgamahesh Manne schrieb am 15.10.2018 um 11:18: > was there any specific reason that you have given max length for varchar is > limited to 10485760 value? > > why you have not given max length for varchar is unlimited like text datatype > ? > > |character varying(/|n|/)|, 

Re: Enabling autovacuum per table

On 10/15/18 11:01 AM, Rijo Roy wrote: Hello Experts, Is there any possibility for autovacuum to work on a user table if we set Alter table sometable set (autovacuum_enabled = true) ; even if the parameter autovacuum = off in Postgresql.conf I am using Postgresql 10 on Linux 6.9. According

Re: Regarding varchar max length in postgres

On Mon, Oct 15, 2018 at 2:42 PM Durgamahesh Manne wrote: > > > On Mon, Oct 15, 2018 at 2:35 PM Durgamahesh Manne < > maheshpostgr...@gmail.com> wrote: > >> >> >> On Mon, Oct 15, 2018 at 2:32 PM Durgamahesh Manne < >> maheshpostgr...@gmail.com> wrote: >> >>> >>> >>> On Fri, Oct 5, 2018 at 8:55 PM

Re: Is there any impact if "#wal_keep_segments = 0 " and "checkpoint_segments = 128" postgresql.conf file.

Thanks a lot. On Mon, 15 Oct 2018 at 14:43, Jehan-Guillaume (ioguix) de Rorthais < iog...@free.fr> wrote: > On Mon, 15 Oct 2018 09:46:47 +0200 > Laurenz Albe wrote: > > > Raghavendra Rao J S V wrote: > > > Is there any impact if "#wal_keep_segments = 0 " and > "checkpoint_segments > > > = 128"

Re: Is there any impact if "#wal_keep_segments = 0 " and "checkpoint_segments = 128" postgresql.conf file.

On Mon, 15 Oct 2018 09:46:47 +0200 Laurenz Albe wrote: > Raghavendra Rao J S V wrote: > > Is there any impact if "#wal_keep_segments = 0 " and "checkpoint_segments > > = 128" postgresql.conf file. If yes,what is the imapct? > > Yes. > - You will have fewer checkpoints requested by data

Re: Regarding varchar max length in postgres

On Mon, Oct 15, 2018 at 2:35 PM Durgamahesh Manne wrote: > > > On Mon, Oct 15, 2018 at 2:32 PM Durgamahesh Manne < > maheshpostgr...@gmail.com> wrote: > >> >> >> On Fri, Oct 5, 2018 at 8:55 PM Adrian Klaver >> wrote: >> >>> On 10/5/18 8:18 AM, Durgamahesh Manne wrote: >>> > Hi >>> > >>> >

Re: Regarding varchar max length in postgres

Durgamahesh Manne schrieb am 15.10.2018 um 11:05: > 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 > >  

Re: Regarding varchar max length in postgres

On Mon, Oct 15, 2018 at 2:32 PM Durgamahesh Manne wrote: > > > On Fri, Oct 5, 2018 at 8:55 PM Adrian Klaver > wrote: > >> On 10/5/18 8:18 AM, Durgamahesh Manne wrote: >> > Hi >> > >> > please let me know the max length of varchar & text in postgres >> >>

Re: Regarding varchar max length in postgres

On Fri, Oct 5, 2018 at 8:55 PM Adrian Klaver wrote: > 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

Enabling autovacuum per table

Hello Experts,  Is there any possibility for autovacuum to work on a user table if we set Alter table sometable set (autovacuum_enabled = true) ; even if the parameter autovacuum = off in Postgresql.conf  I am using Postgresql 10 on Linux 6.9. According to me, it won't work without setting

Re: Is there any impact if "#wal_keep_segments = 0 " and "checkpoint_segments = 128" postgresql.conf file.

Raghavendra Rao J S V wrote: > Is there any impact if "#wal_keep_segments = 0 " and "checkpoint_segments = > 128" > postgresql.conf file. If yes,what is the imapct? Yes. - You will have fewer checkpoints requested by data modification activity. - Crash recovery might take longer. Yours,

Is there any impact if "#wal_keep_segments = 0 " and "checkpoint_segments = 128" postgresql.conf file.

Hi All, Is there any impact if "#wal_keep_segments = 0 " and "checkpoint_segments = 128" postgresql.conf file. If yes,what is the imapct? *checkpoint_segments = 128* # in logfile segments, min 1, 16MB each #checkpoint_timeout = 5min # range 30s-1h #checkpoint_completion_target = 0.5 #

Re: Max number of WAL files in pg_xlog directory for Postgres 9.2 version

Hi John, As you said, I have gone through the document. Which one is correct [(2 + checkpoint_completion_target) * checkpoint_segments + 1 *or* checkpoint_segments + wal_keep_segments + 1 files] for