[GENERAL] pg_stats_repoter error message

[krish050591]

I tried installing pgstatsinfo and pgstatsrepoter.

Whatever i try to give in pg_stats_reporter.ini I get a error message,
particularly whatever i give in repository database, i get an error.
 . Have anyone
installed these monitoring tool. Please help me with the installation.



--
View this message in context: 
http://postgresql.nabble.com/pg-stats-repoter-error-message-tp5864568.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] error on online backup using pg_basebackup tool

[Gerdan Rezende dos Santos]

On Thu, Sep 3, 2015 at 7:20 AM, Martín Marqués 
wrote:

> El 03/09/15 a las 00:54, Gerdan Rezende dos Santos escribió:
> >
> >
> > In other server with same configuration the proccess not complete too!
>
> Add --verbose to the pg_basebackup execution and send over the output
> error message.
>
> I find your procedure confusing, and I'd recommend you *not* to put
> tablespaces in $PGDTA/pg_tblspc/. That directory is for postgres to put
> the links to where the actual tablespace is (normally on another partition)
>
> --
> Martín Marquéshttp://www.2ndQuadrant.com/
> PostgreSQL Development, 24x7 Support, Training & Services
>


This message on verbose mode: pg_basebackup: directory "/data/pg_tblspc/"
exists but is not empty

>From machine A to machine B

pb_basebackup started in machine B, connecting on machine A


On machine B
pb_basebackup -D /data/pg_tblspc/ -h machineA


T.'.A.'.F.'.,
*Gerdan Rezende dos Santos *
*Po*stgreSQL & EnterpriseDB Specialist, Support, Training & Services
+55 (61) 9645-1525

Re: [GENERAL] how to investigate GIN fast updates and cleanup cycles?

[Steve Kehlet]

I wanted to follow up and report we altered the index in question and
turned off fastupdate and we have not seen the issue I originally reported
since. Just for the record I did:

ALTER INDEX my_index SET (fastupdate=off);
SET vacuum_cost_delay=0;
VACUUM VERBOSE my_table_with_that_index;

We looked into the performance impact on the inserts/updates and decided it
was better for us to take the tiny hit on them and thus avoid these
occasional blockages caused by the pending list cleanup cycle.

Thank you Jeff and Tom for the help! I have a number of todos still from
this too, including adjusting our autovacuum settings and looking into
improving that blocked/blocking query.



On Fri, Aug 28, 2015 at 1:44 PM Steve Kehlet  wrote:

> On Fri, Aug 28, 2015 at 1:23 PM Jeff Janes  wrote:
>
>> You should RESET the autovacuum_vacuum_scale_factor for the table.  You
>> don't want it to be vacuumed aggressively, just autoanalyzed aggressively.
>> Sorry if my copy-paste error led you astray on that.
>>
>
> No problem, done, thank you.
>
> There is a bulk load going on right now so a lot of tables are needing
>>> vacuuming. I really need to increase my autovacuum_max_workers.
>>>
>>
>> But those workers all share the same IO throttling amongst themselves.
>> Increasing it mostly just gives you more workers all working more slowly.
>>
>
> Ah, you're right, that won't help.
>
>
>> Assuming your IO subsystem can handle it, you are better off lowering
>> autovacuum_vacuum_cost_delay, which can be done without a server restart
>> (although the change won't take full effect until the existing workers go
>> away and restart).  I also set vacuum_cost_page_hit and
>> vacuum_cost_page_miss to zero and rely exclusively on vacuum_cost_page_dirty
>> to do the throttling.
>>
>
> Thank you for these great suggestions, I will play with them.
>
>>

Re: [GENERAL] error on online backup using pg_basebackup tool

[Gerdan Rezende dos Santos]

On Thu, Sep 3, 2015 at 5:01 PM, Martín Marqués 
wrote:

> 2015-09-03 16:48 GMT-03:00 Gerdan Rezende dos Santos :
> > On Thu, Sep 3, 2015 at 7:20 AM, Martín Marqués 
> > wrote:
> >>
> >> El 03/09/15 a las 00:54, Gerdan Rezende dos Santos escribió:
> >> >
> >> >
> >> > In other server with same configuration the proccess not complete too!
> >>
> >> Add --verbose to the pg_basebackup execution and send over the output
> >> error message.
> >>
> >> I find your procedure confusing, and I'd recommend you *not* to put
> >> tablespaces in $PGDTA/pg_tblspc/. That directory is for postgres to put
> >> the links to where the actual tablespace is (normally on another
> >> partition)
> >
> > This message on verbose mode: pg_basebackup: directory "/data/pg_tblspc/"
> > exists but is not empty
>
> The error message is quite clear, it has to use /data/pg_tblspc/ to
> put a tablespace, but that directory is not empty.
>
> Why are you putting tablespaces inside PGDATA? It really makes no
> sense. Just leave the objects you have there in the default
> tablespace.
>
> Regards,
>
> --
> Martín Marquéshttp://www.2ndQuadrant.com/
> PostgreSQL Development, 24x7 Support, Training & Services
>


This problem, I found in my client ...
Why are you putting in PGDATA table ? He can not say to me
The solution then is to continue using the start_backup , rsync and
stop_backup. ;(

Thanks!

Re: [GENERAL] error on online backup using pg_basebackup tool

[Martín Marqués]

Hi Gerdan,

2015-09-03 17:41 GMT-03:00 Gerdan Rezende dos Santos :
>
> This problem, I found in my client ...
> Why are you putting in PGDATA table ? He can not say to me
> The solution then is to continue using the start_backup , rsync and
> stop_backup. ;(

Or use repmgr. ;)

If you intend to use repmgr3 (will only work with postgres 9.3 and
above) you'll need to pass it the --rsync-only option so it doesn't
use pg_basebackup to clone.

Regards,

-- 
Martín Marquéshttp://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] error on online backup using pg_basebackup tool

[Martín Marqués]

Hi Gerdan,

2015-09-03 18:06 GMT-03:00 Gerdan Rezende dos Santos :
>
> Matín, the client have a BART from EDB.
> EDB BART only use pg_basebackup.
> Its my problem.

AFAIK BART is a backup & recovery tool, and I thought you were trying
to spin new standbys, which is why I recommended repmgr. If you need
to do backups then repmgr is not the way to go. There are other
alternatives.

Regards,

-- 
Martín Marquéshttp://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] error on online backup using pg_basebackup tool

[Martín Marqués]

2015-09-03 16:48 GMT-03:00 Gerdan Rezende dos Santos :
> On Thu, Sep 3, 2015 at 7:20 AM, Martín Marqués 
> wrote:
>>
>> El 03/09/15 a las 00:54, Gerdan Rezende dos Santos escribió:
>> >
>> >
>> > In other server with same configuration the proccess not complete too!
>>
>> Add --verbose to the pg_basebackup execution and send over the output
>> error message.
>>
>> I find your procedure confusing, and I'd recommend you *not* to put
>> tablespaces in $PGDTA/pg_tblspc/. That directory is for postgres to put
>> the links to where the actual tablespace is (normally on another
>> partition)
>
> This message on verbose mode: pg_basebackup: directory "/data/pg_tblspc/"
> exists but is not empty

The error message is quite clear, it has to use /data/pg_tblspc/ to
put a tablespace, but that directory is not empty.

Why are you putting tablespaces inside PGDATA? It really makes no
sense. Just leave the objects you have there in the default
tablespace.

Regards,

-- 
Martín Marquéshttp://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] error on online backup using pg_basebackup tool

[Gerdan Rezende dos Santos]

On Thu, Sep 3, 2015 at 5:53 PM, Martín Marqués 
wrote:

> Hi Gerdan,
>
> 2015-09-03 17:41 GMT-03:00 Gerdan Rezende dos Santos :
> >
> > This problem, I found in my client ...
> > Why are you putting in PGDATA table ? He can not say to me
> > The solution then is to continue using the start_backup , rsync and
> > stop_backup. ;(
>
> Or use repmgr. ;)
>
> If you intend to use repmgr3 (will only work with postgres 9.3 and
> above) you'll need to pass it the --rsync-only option so it doesn't
> use pg_basebackup to clone.
>
> Regards,
>
> --
> Martín Marquéshttp://www.2ndQuadrant.com/
> PostgreSQL Development, 24x7 Support, Training & Services
>


Matín, the client have a BART from EDB.
EDB BART only use pg_basebackup.
Its my problem.

Re: [GENERAL] error on online backup using pg_basebackup tool

[Joseph Kregloh]

On Thu, Sep 3, 2015 at 4:41 PM, Gerdan Rezende dos Santos 
wrote:

> On Thu, Sep 3, 2015 at 5:01 PM, Martín Marqués 
> wrote:
>
>> 2015-09-03 16:48 GMT-03:00 Gerdan Rezende dos Santos :
>> > On Thu, Sep 3, 2015 at 7:20 AM, Martín Marqués 
>> > wrote:
>> >>
>> >> El 03/09/15 a las 00:54, Gerdan Rezende dos Santos escribió:
>> >> >
>> >> >
>> >> > In other server with same configuration the proccess not complete
>> too!
>> >>
>> >> Add --verbose to the pg_basebackup execution and send over the output
>> >> error message.
>> >>
>> >> I find your procedure confusing, and I'd recommend you *not* to put
>> >> tablespaces in $PGDTA/pg_tblspc/. That directory is for postgres to put
>> >> the links to where the actual tablespace is (normally on another
>> >> partition)
>> >
>> > This message on verbose mode: pg_basebackup: directory
>> "/data/pg_tblspc/"
>> > exists but is not empty
>>
>> The error message is quite clear, it has to use /data/pg_tblspc/ to
>> put a tablespace, but that directory is not empty.
>>
>> Why are you putting tablespaces inside PGDATA? It really makes no
>> sense. Just leave the objects you have there in the default
>> tablespace.
>>
>> Regards,
>>
>> --
>> Martín Marquéshttp://www.2ndQuadrant.com/
>> PostgreSQL Development, 24x7 Support, Training & Services
>>
>
>
> This problem, I found in my client ...
> Why are you putting in PGDATA table ? He can not say to me
> The solution then is to continue using the start_backup , rsync and
> stop_backup. ;(
>
>
You can always move the folder out of there. Depending on the version of
PostgreSQL different steps need to be take. If I remember correctly for
version 9.3 you can move the symlink to point to the new directory. Pre 9.3
you need to move the symlink and the pointer inside the pg tables.

-Joseph Kregloh


> Thanks!
>

[GENERAL] Postgresql C extension and SIGSEGV

[Etienne Champetier]

Hi,

We are planning to add a C extension (
https://github.com/petropavel13/pg_rrule) to our shared postgresql cluster,
and wondering what are the risk? (looking for the worst case scenario here)

If there is a SIGSEGV, SIGBUS, SIGABRT ..., is the whole server stopping,
or just the request?

Knowing that the extension is only used in select statement, is there a
risk of (on disk) data corruption?

Is the risk limited to the current database? (the extension will only be
used by 1 application with 1 database, and we prefer not to impact other
applications/databases)

Are there any techniques to limit/mitigate these risks?
(configuration/compile flags/...)

Thanks in advance
Etienne

[GENERAL] pg_basebackup problem for Streaming replication

[Sachin Srivastava]

Dear Experts,


One problem, I am facing that when I am using *pg_basebackup* (on Standby
Server) it’s copying only some files and directories not all like pg_notify
folder etc. That’s why when I am restarting the database on "Standby
Server" after "pg_basebackup" then it’s showing an error below. If you have
an idea why these files/folders(through pg_basebackup) are not copying
completely then tell to me.



*“2015-09-03 16:58:30 IST FATAL:  could not open directory "pg_notify": No
such file or directory”*



*Pg_basebackup command as below:*



*pg_basebackup -D /opt/PostgreSQL/9.1/data/ -v -Fp -l Testbackup -h
172.17.43.84 -U replication >& backup.log*



*Data directory of Standby server after taking the pg_basebackup through
above command*



[root@INN14U-DW1353 data]# pwd

/opt/PostgreSQL/9.1/data

[root@INN14U-DW1353 data]# ls -ltr

total 100

-rwx--. 1 postgres postgres   173 Sep  3 16:55 backup_label

drwx--. 2 postgres postgres  4096 Sep  3 16:55 pg_log

drwx--. 2 postgres postgres  4096 Sep  3 16:55 global

drwx--. 5 postgres postgres  4096 Sep  3 16:55 base

-rwx--. 1 postgres postgres 19158 Sep  3 16:55 postgresql.conf_backup

-rwx--. 1 postgres postgres 19231 Sep  3 16:55 postgresql.conf

drwx--. 2 postgres postgres  4096 Sep  3 16:55 pg_xlog

-rwx--. 1 postgres postgres 4 Sep  3 16:55 PG_VERSION

drwx--. 2 postgres postgres  4096 Sep  3 16:55 pg_twophase

drwx--. 2 postgres postgres  4096 Sep  3 16:55 pg_tblspc

drwx--. 2 postgres postgres  4096 Sep  3 16:55 pg_subtrans

drwx--. 2 postgres postgres  4096 Sep  3 16:55 pg_stat_tmp

drwx--. 2 postgres postgres  4096 Sep  3 16:55 pg_serial

drwx--. 4 postgres postgres  4096 Sep  3 16:55 pg_multixact

-rwx--. 1 postgres postgres  4406 Sep  3 16:55 pg_hba.conf

drwx--. 2 postgres postgres  4096 Sep  3 16:55 main



*Data directory of “Primary Server”*





-bash-4.1$ pwd

/opt/PostgreSQL/9.1/data

-bash-4.1$ ls -ltr

total 124

-rw--- 1 postgres postgres 4 Aug 24 11:25 PG_VERSION

drwx-- 2 postgres postgres  4096 Aug 24 11:25 pg_twophase

drwx-- 2 postgres postgres  4096 Aug 24 11:25 pg_tblspc

drwx-- 2 postgres postgres  4096 Aug 24 11:25 pg_subtrans

drwx-- 2 postgres postgres  4096 Aug 24 11:25 pg_serial

drwx-- 4 postgres postgres  4096 Aug 24 11:25 pg_multixact

-rw--- 1 postgres postgres  1636 Aug 24 11:25 pg_ident.conf

drwx-- 2 postgres postgres  4096 Aug 24 11:25 pg_clog

drwx-- 5 postgres postgres  4096 Aug 24 11:25 base

-rw-r--r-- 1 root root 19158 Aug 28 10:26 postgresql.conf_backup

-rw--- 1 root root  4222 Aug 28 10:37 pg_hba.conf_backup

drwx-- 2 postgres postgres  4096 Aug 31 13:19 main

-rw-rw-r-- 1 postgres postgres 19231 Sep  2 18:42 postgresql.conf

-rw--- 1 postgres postgres  4406 Sep  3 11:13 pg_hba.conf

-rw--- 1 postgres postgres73 Sep  3 11:13 postmaster.pid

-rw--- 1 postgres postgres65 Sep  3 11:13 postmaster.opts

drwx-- 2 postgres postgres  4096 Sep  3 11:13 pg_notify

drwxr-xr-x 2 postgres postgres  4096 Sep  3 11:13 pg_log

drwx-- 2 postgres postgres  4096 Sep  3 11:14 global

drwx-- 3 postgres postgres  4096 Sep  3 11:25 pg_xlog

drwx-- 2 postgres postgres  4096 Sep  3 11:42 pg_stat_tmp

-bash-4.1$


And one more thing when I am checking the pg_basebackup log then it's also
showing below error, why?


[root@INN14U-DW1353 ~]# cat backup.log


pg_basebackup: could not get WAL end position from server


Regards,

Sachin

Re: [GENERAL] get first and last row in one sql as two columns

[Dickson S. Guedes]

On Wed, Sep 02, 2015 at 07:14:40PM -0400, Tom Smith wrote:
> Hi:
> 
> I need to get the first and last tow in one sql like below
> 
> select first(col1), last(col1) from table order by col1

Have you tried the window functions [1] last_value and first_value?

You could use something like:

SELECT first_value(col1) over (order by col1),
   last_value(col1)  over (order by col1)
FROM table;

It should be as simple as that, or I miss your point.


[1] http://www.postgresql.org/docs/current/static/functions-window.html


[]s
--
Dickson S. Guedes
@guediz - http://github.com/guedes


signature.asc
Description: Digital signature

Re: [GENERAL] error on online backup using pg_basebackup tool

[Martín Marqués]

El 03/09/15 a las 00:54, Gerdan Rezende dos Santos escribió:
> 
> 
> In other server with same configuration the proccess not complete too!

Add --verbose to the pg_basebackup execution and send over the output
error message.

I find your procedure confusing, and I'd recommend you *not* to put
tablespaces in $PGDTA/pg_tblspc/. That directory is for postgres to put
the links to where the actual tablespace is (normally on another partition)

-- 
Martín Marquéshttp://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] get first and last row in one sql as two columns

[Tom Smith]

Hi:

The window function works for me (with adding  limit 1 in the end to output
only one row
 needed instead of many duplicate rows).

thanks very much.

On Thu, Sep 3, 2015 at 6:51 AM, Dickson S. Guedes 
wrote:

> On Wed, Sep 02, 2015 at 07:14:40PM -0400, Tom Smith wrote:
> > Hi:
> >
> > I need to get the first and last tow in one sql like below
> >
> > select first(col1), last(col1) from table order by col1
>
> Have you tried the window functions [1] last_value and first_value?
>
> You could use something like:
>
> SELECT first_value(col1) over (order by col1),
>last_value(col1)  over (order by col1)
> FROM table;
>
> It should be as simple as that, or I miss your point.
>
>
> [1] http://www.postgresql.org/docs/current/static/functions-window.html
>
>
> []s
> --
> Dickson S. Guedes
> @guediz - http://github.com/guedes
>

[GENERAL] Installation of BDR from source

[Ray Stell]

This doc: http://bdr-project.org/docs/stable/installation-source.html

at section: 3.3.3. Installation of BDR from source

needs to have a "make install" added here:

cd /path/to/bdr-plugin-source/
PATH=/path/to/install:"$PATH" ./configure
make -j4 -s all



--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] get first and last row in one sql as two columns

[Thomas Kellerer]

Tom Smith schrieb am 03.09.2015 um 14:11:
>> 
>> SELECT first_value(col1) over (order by col1),
>>last_value(col1)  over (order by col1)
>> FROM table;
>> 

> The window function works for me (with adding limit 1 in the end to output 
> only one row
> needed instead of many duplicate rows).


If that works for you with a LIMIT 1, then I don't understand why 

   select min(col1), max(col1) 
   from table

doesn't work for you



 



-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] bdr admin role

[Craig Ringer]

Please show your connection strings, and the contents of bdr.bdr_nodes
and bdr.bdr_connections.

On 3 September 2015 at 05:00, Ray Stell  wrote:
> This doc specifies to initdb with the admin user "postgres,"
> http://bdr-project.org/docs/stable/quickstart-instances.html
> but if I do that the supervisor falls over with:
> $ cat bdr5598.log
> LOG:  registering background worker "bdr supervisor"
> LOG:  database system was shut down at 2015-09-02 16:04:45 EDT
> LOG:  starting up replication identifier with ckpt at 0/171EBF8
> LOG:  MultiXact member wraparound protections are now enabled
> LOG:  starting background worker process "bdr supervisor"
> LOG:  autovacuum launcher started
> LOG:  database system is ready to accept connections
> LOG:  Created database bdr_supervisordb (oid=16384) during BDR startup
> LOG:  worker process: bdr supervisor (PID 21666) exited with exit code 1
> FATAL:  role "postgresql" does not exist
> LOG:  starting background worker process "bdr supervisor"
>
> It works if I init with "-U postgresql"
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general



-- 
 Craig Ringer   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Installation of BDR from source

[Craig Ringer]

On 3 September 2015 at 21:18, Ray Stell  wrote:
> This doc: http://bdr-project.org/docs/stable/installation-source.html
>
> at section: 3.3.3. Installation of BDR from source
>
> needs to have a "make install" added here:
>
> cd /path/to/bdr-plugin-source/
> PATH=/path/to/install:"$PATH" ./configure
> make -j4 -s all

Thanks, now fixed in the devel tree.

-- 
 Craig Ringer   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general