Dear All,
Thanks for your support.
I am using postgresql 9.3.6 on RHEL6.6 machine.
In my production environment, Some script are executing automatically
through cron.
These script are performing insert and update operation.
Also, every hour,i am performing VACUUM and REINDEX operation on table.
Adding subject line
Dear All,
Thanks for your support.
I am using postgresql 9.3.6 on RHEL6.6 machine.
In my production environment, Some script are executing automatically
through cron.
These script are performing insert and update operation.
Also, every hour,i am performing VACUUM and REI
Hi,
I was doing some tests with backup, replication, standby. After promoting a
standby server, I found my db in a condition that raises me an error while
trying to update a particular tuple.
Below here you can se my UPDATE statment and the error raised.
The select * from stato where id=409; execut
On 21 December 2016 at 09:59, Yogesh Sharma wrote:
> Also, every hour,i am performing VACUUM and REINDEX operation on table.
Why are you running REINDEX every hour? That's a very unusual thing to
do, you'd need a pretty good reason for that.
--
If you can't see the forest for the trees,
Cut the
Dear Alban,
In my production system, there are lot of read write operation performed
every hour.
So, i am thinking, if i can add check during REINDEX operation nothing
update and insert operation performed.
Is it possible?
Regards,
Yogesh
On Wednesday, December 21, 2016, Alban Hertroys wrote:
if you want to reduce the impact of reindex in your inserts and updates
operations why don't you try drop index and CREATE INDEX CONCURRENTLY.
As Alban said you I don't recommend you to execute reindex every hour, at
least try to reduce the scope of the reindex to the minimal object.
2016-12-
Dear All,
I understood below point and i will do the same on my system.
Can i close db session before REINDEX operation and again start db session
after REINDEX completed?
Regards,
Yogesh
On Wednesday, December 21, 2016, Jaime Soler wrote:
> if you want to reduce the impact of reindex in you
On 21/12/16 05:24, Tom Lane wrote:
> James Zhou writes:
>> - *But their sorting order seems to be undefined. Can anyone comment
>> the sorting rules?*
>
> Well, it would depend on lc_collate, which you have not told us, and
> it would also depend on how well your platform's strcoll()
On Wed, Dec 21, 2016 at 4:02 AM, Yogesh Sharma wrote:
> Also, every hour,i am performing VACUUM and REINDEX operation on table.
Why?
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Dear Vick,
I have responded on previously mail.
In my production system, there are lot of read write operation performed
every hour.
Regards,
yogesh
On Wednesday, December 21, 2016, Vick Khera wrote:
> On Wed, Dec 21, 2016 at 4:02 AM, Yogesh Sharma > wrote:
> > Also, every hour,i am performin
> In my production system, there are lot of read write operation performed
> every hour.
Apologies if this sounds patronising but I just wanted to check - you know that
indexes are updated automatically when write operations occur, right?
This email is confidential. If you are not the intende
Dear Steven,
Thanks for your reply.
Yes. You are correct.
Regards,
Yogesh
On Wednesday, December 21, 2016, Steven Winfield <
steven.winfi...@cantabcapital.com> wrote:
> > In my production system, there are lot of read write operation
> performed every hour.
>
>
>
> Apologies if this sounds patr
On Wed, Dec 21, 2016 at 2:56 AM, Kyotaro HORIGUCHI <
horiguchi.kyot...@lab.ntt.co.jp> wrote:
> A PostgreSQL database with encoding=UTF8 just accepts the whole
> range of Unicode, regardless that a character is defined for the
> code or not.
Interesting... when I converted my application and databa
On Wed, Dec 21, 2016 at 8:43 AM, Yogesh Sharma
wrote:
> I have responded on previously mail.
>
>
apparently you started two separate threads...
> In my production system, there are lot of read write operation performed
> every hour.
>
That still doesn't answer why you feel you need to run rein
>>> In my production system, there are lot of read write operation performed
>>> every hour.
>>Apologies if this sounds patronising but I just wanted to check - you know
>>that indexes are updated automatically when write operations occur, right?
>Yes. You are correct.
OK - I think the folks her
On 21 December 2016 at 12:51, Yogesh Sharma wrote:
> Dear Alban,
>
> In my production system, there are lot of read write operation performed
> every hour.
That does not explain why you're performing REINDEX on that system;
rather, it explains why you should NOT be performing REINDEX.
VACUUM is
I have a big,big table, this big table is referenced by another tables
by foreign keys.
I have a performance issues so i decide partition it by month ->
...,table_201610,table_201611,table_201612,...
after i have been created the partition tables i try to insert data but
foreign key constrai
On 12/21/2016 01:51 AM, Tom DalPozzo wrote:
Hi,
I was doing some tests with backup, replication, standby. After
promoting a standby server, I found my db in a condition that raises me
an error while trying to update a particular tuple.
Below here you can se my UPDATE statment and the error raised
well if you would like to cancel o terminate a session before start
inserting or updating data , you can use pg_cancel_backend or
pg_terminate_backend using pid returning from select pid pg_stat_activity
where state='active' ..
2016-12-21 13:28 GMT+01:00 Yogesh Sharma :
> Dear All,
>
> I understo
Hi,
> First I would find what base/16384/29153 actually is. So in the database
> where stato is:
>
> select relname from pg_class where relfilenode = 29153;
>
below here the query you suggested, showing that file belongs to stato
table as expected.
ginopino=# select relname from pg_class where r
Vick Khera writes:
> On Wed, Dec 21, 2016 at 2:56 AM, Kyotaro HORIGUCHI <
> horiguchi.kyot...@lab.ntt.co.jp> wrote:
>> A PostgreSQL database with encoding=UTF8 just accepts the whole
>> range of Unicode, regardless that a character is defined for the
>> code or not.
> Interesting... when I conver
On 12/21/2016 08:17 AM, Tom DalPozzo wrote:
Hi,
First I would find what base/16384/29153 actually is. So in the
database where stato is:
select relname from pg_class where relfilenode = 29153;
below here the query you suggested, showing that file belongs to stato
table as expecte
Hi, Yes it is set in both the user's .profile and also in /etc/profile. In both files it is set using:LD_LIBRARY_PATH=/usr/sfw/lib:/usr/postgres/9.6-pgdg/libexport LD_LIBRARY_PATHThat said, if I execute the followingsu passwordsu postgresecho $LD_LIBRARY_PATHthen echo returns blank. Echo will
Le lun. 19 déc. 2016 à 18:52, Tom Lane a écrit :
> Since the Postgres parser doesn't have any special knowledge about
> the meaning of the -> and || operators, it gives them the same precedence
>
Thanks for clarifying the situation here.
Do you have any reason for giving the same precedence on
Hi all,
I have a PostgreSQL 9.5.4 and a PostgreSQL 9.6.1 instance installed on the same
host. I dump the 9.5.4 instance with:
pg_dump -h localhost -p 5438 -C -c -F d -j 2 -f /var/tmp/exp/ test
.. which runs fine. I get the output as expected:
postgres@pgbox:/home/postgres/ [PG954] ls /var/t
We are running a PITR tests on Postgresql 9.5.4 (moving up from 9.1.18) and
finding that postgres seems to be archiving an invalid WAL (full of nulls)
in cases when a WAL is preallocated on shutdown. Don't see this issue
without preallocation during restartpoint. We use this process on 9.1.18
and
On 12/21/2016 09:22 AM, Daniel Westermann wrote:
Hi all,
I have a PostgreSQL 9.5.4 and a PostgreSQL 9.6.1 instance installed on
the same host. I dump the 9.5.4 instance with:
pg_dump -h localhost -p 5438 -C -c -F d -j 2 -f /var/tmp/exp/ test
.. which runs fine. I get the output as expected:
po
It does
Sent from my Phone
> On 21 Dec 2016, at 18:40, Andreas Kretschmer wrote:
>
>
>
>> Am 21.12.2016 um 18:22 schrieb Daniel Westermann:
>>
>>
>> Now I try to import into 9.6.1 => the instance is not running but the
>> environment is set:
>>
>> postgres@pgbox:/home/postgres/ [PG961] n
Am 21.12.2016 um 18:22 schrieb Daniel Westermann:
Now I try to import into 9.6.1 => the instance is not running but the
environment is set:
postgres@pgbox:/home/postgres/ [PG961] netstat -na | grep 5439
postgres@pgbox:/home/postgres/ [PG961] echo $PGPORT
5439
postgres@pgbox:/home/postgre
On Wed, Dec 21, 2016 at 6:22 PM, Daniel Westermann
wrote:
> I have a PostgreSQL 9.5.4 and a PostgreSQL 9.6.1 instance installed on the
> same host.
.
> What do I miss? I can give any port to pg_restore and it just seems to be
> fine.
, are you by chance using debian/ubuntu/any deriva
Daniel Westermann writes:
> postgres@pgbox:/home/postgres/ [PG961] pg_restore -h localhost -p 5439 -F d
> -C -j 2 /var/tmp/exp/
> This runs fine but where does it connect to? Nothing is listening on port
> 5439.
Given the lack of a -d switch, I'd expect it not to try to connect
anywhere, jus
=?UTF-8?Q?Micka=C3=ABl_Le_Baillif?= writes:
> Do you have any reason for giving the same precedence on those operators ?
> A small survey among my colleagues and friends tends to believe that our
> human brain implicitly gives a greater priority to the '->' operator.
PG's operator precedence rule
>
> Is there an index on this table?
>>
>
> Have you tried a REINDEX on it?
>
> yes there is an index on id field. I tried REINDEX. Nothing changes but I
notice now (but perhaps it was like that even before reindexing) that every
time I issue that UPDATE query, the number of the block it can't read
>> postgres@pgbox:/home/postgres/ [PG961] pg_restore -h localhost -p 5439 -F d
>> -C -j 2 /var/tmp/exp/
>>
>> This runs fine but where does it connect to? Nothing is listening on port
>> 5439.
>Given the lack of a -d switch, I'd expect it not to try to connect
>anywhere, just emit the restor
On 12/19/2016 8:15 PM, r...@salepointdata.com wrote:
Yes it is set in both the user's .profile and also in
/etc/profile. In both files it is set using:
LD_LIBRARY_PATH=/usr/sfw/lib:/usr/postgres/9.6-pgdg/lib
export LD_LIBRARY_PATH
That said, if I execute the following
su password
su postgr
2016-12-21 20:29 GMT+01:00 Daniel Westermann <
daniel.westerm...@dbi-services.com>:
> >> postgres@pgbox:/home/postgres/ [PG961] pg_restore -h localhost -p 5439
> -F d -C -j 2 /var/tmp/exp/
> >>
> >> This runs fine but where does it connect to? Nothing is listening on
> port 5439.
>
> >Given the la
Hi.
Perhaps i've sent it to wrong mail-list previously.
After the upgrade postgresql had a strange behavior. After the crash, server
launch now take a long time, up to 30-40 minutes sometimes. When connecting to
the server (as well as in the log), the message - "the database system is
starting
Hi!
i need disable inheritance from many tables in a query like
"delete from pg_inherits where inhparent=20473" instead alter table ...
but is safe? which is the risk for database if i delete it?
Edmundo Robles writes:
> i need disable inheritance from many tables in a query like
> "delete from pg_inherits where inhparent=20473" instead alter table ...
> but is safe? which is the risk for database if i delete it?
This seems really dangerous. You're certainly missing the pg_depe
On 12/21/2016 11:29 AM, Daniel Westermann wrote:
Providing "-d" gives a meaningful message at least:
postgres@pgbox:/home/postgres/ [PG961] pg_restore -h localhost -p
=== -d postgres -F d -C /var/tmp/exp/
pg_restore: [archiver (db)] connection to database "postgres" failed:
invalid port num
On 12/21/2016 12:59 PM, Guillaume Lelarge wrote:
2016-12-21 20:29 GMT+01:00 Daniel Westermann
mailto:daniel.westerm...@dbi-services.com>>:
>> postgres@pgbox:/home/postgres/ [PG961] pg_restore -h localhost -p 5439
-F d -C -j 2 /var/tmp/exp/
>>
>> This runs fine but where does it conn
On 12/21/2016 02:17 PM, n...@olansoft.com wrote:
Hi.
Perhaps i've sent it to wrong mail-list previously.
After the upgrade postgresql had a strange behavior. After the crash, server launch now
take a long time, up to 30-40 minutes sometimes. When connecting to the server (as well
as in the lo
I figured out that I need to use the function CHR to enter supplementary
unicode characters (code points > , i.e. planes 1 - F), e.g.
insert into unicode(id, string) values(100, CHR(128120)); -- a mojo
character, https://unicodelookup.com/#0x1f478/1
insert into unicode(id, string) values(101,
On Wed, Dec 21, 2016 at 3:36 PM, Edmundo Robles wrote:
> Hi!
>
> i need disable inheritance from many tables in a query like
>
> "delete from pg_inherits where inhparent=20473" instead alter table ...
>
> but is safe? which is the risk for database if i delete it?
You could change the s
On 12/21/2016 10:06 AM, Tom DalPozzo wrote:
Is there an index on this table?
Have you tried a REINDEX on it?
yes there is an index on id field. I tried REINDEX. Nothing changes but
I notice now (but perhaps it was like that even before reindexing) that
every time I issue that UPDAT
James Zhou writes:
> The format U&'\03B1' only works for chars between -
Kyotaro-san already pointed you to the right answer on that:
you have to use "\+nn" for six-digit code points in the U&
string syntax.
regards, tom lane
--
Sent via pgsql-general mai
22.12.2016, 04:21, "Adrian Klaver" :
> On 12/21/2016 02:17 PM, n...@olansoft.com wrote:
>> Hi.
>>
>> Perhaps i've sent it to wrong mail-list previously.
>>
>> After the upgrade postgresql had a strange behavior. After the crash,
>> server launch now take a long time, up to 30-40 minutes somet
On 12/21/2016 04:16 PM, n...@olansoft.com wrote:
22.12.2016, 04:21, "Adrian Klaver" :
On 12/21/2016 02:17 PM, n...@olansoft.com wrote:
Hi.
Perhaps i've sent it to wrong mail-list previously.
After the upgrade postgresql had a strange behavior. After the crash, server launch now
take a l
I’m toying around with the wire protocol and came across something I don’t
understand.
I created a table with two columns, one called “id” and one called “señor”.
When I select from that table I get the list of columns and while its fairly
easy to identify the column with the name “id”, I’m not
22.12.2016, 06:31, "Adrian Klaver" :
>
> Alright looks like it doing the correct thing.
>
> Now if I am following you say you see the issue starting with 9.5+. As
> it so happens that is when checkpoint_segments was replaced with
> max_wal_size:
Yes, in 9.4 recovery process began instantly(or near
On Thu, Dec 22, 2016 at 8:25 AM, Rui Pacheco wrote:
> I’m toying around with the wire protocol and came across something I don’t
> understand.
>
> I created a table with two columns, one called “id” and one called “señor”.
> When I select from that table I get the list of columns and while its f
On 12/21/2016 06:53 PM, n...@olansoft.com wrote:
22.12.2016, 06:31, "Adrian Klaver" :
Alright looks like it doing the correct thing.
Now if I am following you say you see the issue starting with 9.5+. As
it so happens that is when checkpoint_segments was replaced with
max_wal_size:
If yo
Hi
I have installed and configured pgAudit extension and the pgAudit Analyzer
https://github.com/pgaudit/pgaudit/tree/REL9_5_STABLE
https://github.com/pgaudit/pgaudit_analyze
I have several databases in the PostgreSQL instance and created the extension
and setup the pgaudit analyzer for these dat
>
>
>>
> If it where me I would use one of the -X methods:
>
> https://www.postgresql.org/docs/9.5/static/app-pgbasebackup.html
>
>
>
>> To me that looks like an issue with the associated TOAST table. I do not
> have a suggestion at this time. Maybe this rings a bell with someone else.
>
> --
> Adr
>It isn't consistent but it's by purpose. And there's a really good reason for
>that behaviour. There's no issue with psql connecting to a >default database
>because psql doesn't do anything by itself. pg_restore will do something to
>the database it connects to. It might drop >some objects, cre
55 matches
Mail list logo
