Re: [ADMIN] Reduce the size of the archived-log directory
The size of your directory will increase by 16mb on every checkpoint (16Mb or 5 mins) so, even if your DB is completely idle you will get 16Mb per 5 mins, make the calculations and you'll see the space you need per day. Vasilis Ventirozos On Thu, Mar 28, 2013 at 8:54 AM, Hoàng Thanh Toàn - DB wrote: > Hello Vasilis, > > Thanks for your reply, > > I thinks your archiving is successful for transfering WAL segments. > > archive_command = 'test ! -f /mnt/FRA/ArchivedLog/%f && cp %p > /mnt/FRA/ArchivedLog/%f'# command to use to archive a > logfile segment > > You are right that I am archiving at a local disk, but what it shall > affect the size of the archived WAL directory. > > ** ** > > ## postgresql.conf (standalone > server)### > > # - Settings - > > ** ** > > wal_level = archive # minimal, archive, or > hot_standby > > # (change > requires restart) > > #fsync = on # turns forced > synchronization on or off > > #synchronous_commit = on # synchronization level; on, off, > or local > > #wal_sync_method = fsync # the default is the first option** > ** > > # supported by > the operating system: > > # > open_datasync > > # fdatasync > (default on Linux) > > # fsync > > # > fsync_writethrough > > # open_sync* > *** > > #full_page_writes = on# recover from > partial page writes > > #wal_buffers = -1# min 32kB, -1 sets based > on shared_buffers > > # (change > requires restart) > > #wal_writer_delay = 200ms# 1-1 milliseconds > > ** ** > > #commit_delay = 0 # range 0-10, in > microseconds > > #commit_siblings = 5 # range 1-1000 > > # - Checkpoints - > > ** ** > > checkpoint_segments = 7 # in logfile segments, min 1, > 16MB each > > #checkpoint_timeout = 5min # range 30s-1h > > #checkpoint_completion_target = 0.5# checkpoint target > duration, 0.0 - 1.0 > > #checkpoint_warning = 30s# 0 disables > > ** ** > > # - Archiving - > > ** ** > > archive_mode = on # allows archiving to be done > > # (change requires restart) > > > archive_command = 'test ! -f /mnt/FRA/ArchivedLog/%f && cp %p > /mnt/FRA/ArchivedLog/%f'# command to use to archive a > logfile segment > > archive_timeout = 180# force a logfile segment > switch after this > > # number of seconds; 0 > disables > > # - Master Server - > > ** ** > > # These settings are ignored on a standby server > > ** ** > > #max_wal_senders = 0 # max number of walsender > processes > > # (change requires restart) > > > #wal_sender_delay = 1s # walsender cycle time, 1-1 > milliseconds > > #wal_keep_segments = 0 # in logfile segments, 16MB each; > 0 disables > > #vacuum_defer_cleanup_age = 0 # number of xacts by which cleanup is > delayed > > #replication_timeout = 60s # in milliseconds; 0 disables > > #synchronous_standby_names = ''# standby servers that provide sync rep > > > # comma-separated list of > application_name > > # from standby(s); '*' = > all > > -- > > ** ** > > *From:* Vasilis Ventirozos [mailto:v.ventiro...@gmail.com] > *Sent:* Thursday, March 28, 2013 1:29 PM > *To:* Hoàng Thanh Toàn - DB > *Subject:* Re: [ADMIN] Reduce the size of the archived-log directory > > ** ** > > Hello, > > its either your archiving is failing to transfer wal segments (check > archiving_command) , or you are archiving at a local disk (you shouldnt, > check archiving command) or you have an astronomical archive_keep_segments > value > > ** ** > > ** ** > > Vasilis Ventirozos > > ** ** > > ** ** > > On Thu, Mar 28, 2013 at 8:10 AM, Hoàng Thanh Toàn - DB > wrote: > > Dear all, > > Please help me. > > Please show me how to reduce the size of the archived-log dir
[ADMIN] VACUUM ANALYZE AND ANALYZE ISSUE
Hi, I was running vacuum analyze on one of heavily bloated table. After the vacuum analyze completed . I did explain on a query . It was going for hash join and sequential scan. Then i issues ANALYZE BLOATED_TABLE; Then when ran explain query , surprisingly it was going for index scan . This was observed in postgres 9.0.1. Ideally VACUUM ANALYSE must have done VACCUM + ANALYZE. But ANALYZE part was pending. Which was completed when i ran analyze on that table . Can any one explain this? Did i miss anything. Rgrds Suhas -- View this message in context: http://postgresql.1045698.n5.nabble.com/VACUUM-ANALYZE-AND-ANALYZE-ISSUE-tp5749963.html Sent from the PostgreSQL - admin mailing list archive at Nabble.com. -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] Dump only functions
Thank you. I tried pg_extractor and it work almost perfect. I have just one
question. We do overloading of a function name (using different parameters) and
I think it puts all overlaoded functions in the same file (those with the same
file name that is). Is there a way to separate them?
Thanks again for everyones help.
From: Scott Mead [sco...@openscg.com]
Sent: Wednesday, March 27, 2013 1:49 PM
To: Raghavendra
Cc: Keith Ouellette; pgsql-admin@postgresql.org
Subject: Re: [ADMIN] Dump only functions
On Wed, Mar 27, 2013 at 1:43 PM, Raghavendra
mailto:raghavendra@enterprisedb.com>>
wrote:
On Wed, Mar 27, 2013 at 10:53 PM, Keith Ouellette
mailto:keith.ouelle...@airgas.com>> wrote:
Is there a way to dump only functions to a directory with each function as its
own file in SQL format?
AFAIK, there's no direct way to dump each function to a separate file. However,
you can use system-defined function or system table to retrieve function
structure and then write them to separate file each by using bash or perl
scripting.
select prosrc from pg_proc where proname='foo';
or
select pg_get_functiondef('foo(integer)'::regprocedure::oid);
or
Use other sources like pg_extractor tools.
http://www.keithf4.com/pg_extractor/
+1 here. pg_extractor is my personal favorite for this type of thing. It can
give you all of your objects as separate files if you'd like.
--Scott
---
Regards,
Raghavendra
EnterpriseDB Corporation
Blog: http://raghavt.blogspot.com/
Thanks,
Keith
Re: [ADMIN] Dump only functions
something like this would probably work too
for a in `echo "SELECT p.proname
FROM pg_catalog.pg_proc p
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace
WHERE pg_catalog.pg_function_is_visible(p.oid)
AND n.nspname <> 'pg_catalog'
AND n.nspname <> 'information_schema'"|psql -A -t test`;
do echo "SELECT pg_get_functiondef('$a'::regproc);"|psql -A -t test
>$a.function.sql ;done
On Thu, Mar 28, 2013 at 5:29 PM, Keith Ouellette wrote:
> Thank you. I tried pg_extractor and it work almost perfect. I have just
> one question. We do overloading of a function name (using different
> parameters) and I think it puts all overlaoded functions in the same file
> (those with the same file name that is). Is there a way to separate them?
>
>
>
> Thanks again for everyones help.
>
>
>
>
> --
> *From:* Scott Mead [sco...@openscg.com]
> *Sent:* Wednesday, March 27, 2013 1:49 PM
> *To:* Raghavendra
> *Cc:* Keith Ouellette; pgsql-admin@postgresql.org
> *Subject:* Re: [ADMIN] Dump only functions
>
> On Wed, Mar 27, 2013 at 1:43 PM, Raghavendra <
> raghavendra@enterprisedb.com> wrote:
>
>> On Wed, Mar 27, 2013 at 10:53 PM, Keith Ouellette <
>> keith.ouelle...@airgas.com> wrote:
>>
>>> Is there a way to dump only functions to a directory with each
>>> function as its own file in SQL format?
>>>
>> AFAIK, there's no direct way to dump each function to a separate file.
>> However, you can use system-defined function or system table to retrieve
>> function structure and then write them to separate file each by using bash
>> or perl scripting.
>>
>> select prosrc from pg_proc where proname='foo';
>> or
>> select pg_get_functiondef('foo(integer)'::regprocedure::oid);
>> or
>> Use other sources like pg_extractor tools.
>> http://www.keithf4.com/pg_extractor/
>>
>
> +1 here. pg_extractor is my personal favorite for this type of thing.
> It can give you all of your objects as separate files if you'd like.
>
> --Scott
>
>
>>
>> ---
>> Regards,
>> Raghavendra
>> EnterpriseDB Corporation
>> Blog: http://raghavt.blogspot.com/
>>
>>
>>
>>>
>>>
>>> Thanks,
>>>
>>> Keith
>>>
>>>
>>>
>>
>>
>
Re: [ADMIN] VACUUM ANALYZE AND ANALYZE ISSUE
suhas.basavaraj12 wrote: > This was observed in postgres 9.0.1. You might want to review the bug fixes in 9.0 maintenance releases and see if any of them could be responsible for what you saw: http://www.postgresql.org/docs/9.0/static/release-9-0-2.html http://www.postgresql.org/docs/9.0/static/release-9-0-3.html http://www.postgresql.org/docs/9.0/static/release-9-0-4.html http://www.postgresql.org/docs/9.0/static/release-9-0-5.html http://www.postgresql.org/docs/9.0/static/release-9-0-6.html http://www.postgresql.org/docs/9.0/static/release-9-0-7.html http://www.postgresql.org/docs/9.0/static/release-9-0-8.html http://www.postgresql.org/docs/9.0/static/release-9-0-9.html http://www.postgresql.org/docs/9.0/static/release-9-0-10.html http://www.postgresql.org/docs/9.0/static/release-9-0-11.html http://www.postgresql.org/docs/9.0/static/release-9-0-12.html ... or just apply all of those bug fixes and see if you can make it happen again. In general, it pays to apply fixes as they become available. http://www.postgresql.org/support/versioning/ -- Kevin Grittner EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
[ADMIN] select exact term
Is there a way to create a select statement that will select a record if the exact term is found in a field that contains the text to describe something? If I create a select statement using WHERE description LIKE 'art' I get every record that has words like depart, start and so on. If I create a select statement using WHERE description = 'art' I get no results even though the word art is in some records description field. Thanks Marc
Re: [ADMIN] select exact term
On Thu, Mar 28, 2013 at 1:51 PM, Marc Fromm wrote: > Is there a way to create a select statement that will select a record if > the exact term is found in a field that contains the text to describe > something? > > ** ** > > If I create a select statement using WHERE description LIKE ‘art’ I get > every record that has words like depart, start and so on. > > If I create a select statement using WHERE description = ‘art’ I get no > results even though the word art is in some records description field. > > ** ** > > Thanks > > ** ** > > Marc > http://www.postgresql.org/docs/9.2/static/functions-matching.html -- Douglas J Hunley (doug.hun...@gmail.com) Twitter: @hunleyd Web: douglasjhunley.com G+: http://goo.gl/sajR3
Re: [ADMIN] select exact term
On Thu, Mar 28, 2013 at 10:51 AM, Marc Fromm wrote: > Is there a way to create a select statement that will select a record if > the exact term is found in a field that contains the text to describe > something? > > ** ** > > If I create a select statement using WHERE description LIKE ‘art’ I get > every record that has words like depart, start and so on. > > If I create a select statement using WHERE description = ‘art’ I get no > results even though the word art is in some records description field. > Use a regular expression instead of LIKE, and the left- and right-word-boundary expressions (see section 9.7 of the Postgres manual): db=> select 'the quick brown fox' ~ '[[:<:]]brown[[:>:]]'; ?column? -- t => select 'the quick brown fox' ~ '[[:<:]]own[[:>:]]'; ?column? -- f Craig
Re: [ADMIN] select exact term
I am struggling with the syntax. In php I create my where clause as shown, using ~* for case insensitive: $search = "art"; $strSQL2 = "WHERE (title ~* [[:<:]]'$search'[[:>:]] OR description ~* [[:<:]]'$search'[[:>:]]) "; When executed zero records are returned even though the ILIKE statement shown below returns records that do have the word art. $search = "art"; $strSQL2 = "WHERE (title ILIKE '%$search%' OR description ILIKE '%$search%') "; Thanks for the insight. From: Craig James [mailto:cja...@emolecules.com] Sent: Thursday, March 28, 2013 11:05 AM To: Marc Fromm Cc: pgsql-admin@postgresql.org Subject: Re: [ADMIN] select exact term On Thu, Mar 28, 2013 at 10:51 AM, Marc Fromm mailto:marc.fr...@wwu.edu>> wrote: Is there a way to create a select statement that will select a record if the exact term is found in a field that contains the text to describe something? If I create a select statement using WHERE description LIKE 'art' I get every record that has words like depart, start and so on. If I create a select statement using WHERE description = 'art' I get no results even though the word art is in some records description field. Use a regular expression instead of LIKE, and the left- and right-word-boundary expressions (see section 9.7 of the Postgres manual): db=> select 'the quick brown fox' ~ '[[:<:]]brown[[:>:]]'; ?column? -- t => select 'the quick brown fox' ~ '[[:<:]]own[[:>:]]'; ?column? -- f Craig
Re: [ADMIN] select exact term
Marc Fromm writes: > I am struggling with the syntax. In php I create my where clause as shown, > using ~* for case insensitive: > $search = "art"; > $strSQL2 = "WHERE (title ~* [[:<:]]'$search'[[:>:]] OR description ~* > [[:<:]]'$search'[[:>:]]) "; > When executed zero records are returned even though the ILIKE statement shown > below returns records that do have the word art. Your php app must not be bothering to check for errors :-( ... that's invalid SQL syntax. The bracket constructs are part of the regexp string and need to be inside the single quotes. regards, tom lane -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] select exact term
Thanks Tom, I just discovered that. I reworked the php so the quotes surround the regexp. -Original Message- From: Tom Lane [mailto:t...@sss.pgh.pa.us] Sent: Thursday, March 28, 2013 1:07 PM To: Marc Fromm Cc: Craig James; pgsql-admin@postgresql.org Subject: Re: [ADMIN] select exact term Marc Fromm writes: > I am struggling with the syntax. In php I create my where clause as shown, > using ~* for case insensitive: > $search = "art"; > $strSQL2 = "WHERE (title ~* [[:<:]]'$search'[[:>:]] OR description ~* > [[:<:]]'$search'[[:>:]]) "; > When executed zero records are returned even though the ILIKE statement shown > below returns records that do have the word art. Your php app must not be bothering to check for errors :-( ... that's invalid SQL syntax. The bracket constructs are part of the regexp string and need to be inside the single quotes. regards, tom lane -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] select exact term
On Thu, Mar 28, 2013 at 10:51 AM, Marc Fromm wrote:
> Is there a way to create a select statement that will select a record if the
> exact term is found in a field that contains the text to describe something?
In addition to what has been suggested before you may find interesting
full text search abilities
http://www.postgresql.org/docs/9.2/static/textsearch.html. It allows
to do more complex searches like this:
SELECT title, ts_rank_cd(textsearch, query) AS rank
FROM apod, to_tsquery('neutrino|(dark & matter)') query
WHERE query @@ textsearch
ORDER BY rank DESC
LIMIT 10;
--
Kind regards,
Sergey Konoplev
Database and Software Consultant
Profile: http://www.linkedin.com/in/grayhemp
Phone: USA +1 (415) 867-9984, Russia +7 (901) 903-0499, +7 (988) 888-1979
Skype: gray-hemp
Jabber: gray...@gmail.com
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
[ADMIN] Grant tables cascade to sequence?
Hi, I encounter the same issue often: Granted update/insert to an user but forgot to grant it on the related sequence. It's hard to understand that an user has write access on table but not on necessary sequences. I think the grant on tables should cascade to related sequences. What do you think? -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
