Обсуждение: Database in psql

Поиск
Список
Период
Сортировка

Database in psql

От
teyking2003
Дата:
i'm a very fresh on psql+linux setup.
at my side, i have linux ubuntu 2.6.22-14 server + psql 8.2.5+bluedragon web
server. im confuse with vacuum steps, normally i use
*psql database_name
database_name=# vacuum;/vacuum full/ vacuum full analyst*
to perform basic maintenance to the system. but the user sometimes was feel
very slow while accessing using browser, information search will hang over
there.

Need to ask all the expert here, i have a schedule running on every night to
backup the database to name.sql file, i found this files was grown everyday
until 1.3GB now. is it the slow because of the database? or any idea that i
can reduce the database size.



--
View this message in context: http://postgresql.1045698.n5.nabble.com/Database-in-psql-tp5728767.html
Sent from the PostgreSQL - admin mailing list archive at Nabble.com.


Re: Database in psql

От
teyking2003
Дата:
hi any expert here



--
View this message in context: http://postgresql.1045698.n5.nabble.com/Database-in-psql-tp5728767p5728962.html
Sent from the PostgreSQL - admin mailing list archive at Nabble.com.


Re: Database in psql

От
Scott Marlowe
Дата:
On Thu, Oct 18, 2012 at 2:03 AM, teyking2003 <tchongking@hotmail.com> wrote:
> i'm a very fresh on psql+linux setup.
> at my side, i have linux ubuntu 2.6.22-14 server + psql 8.2.5+bluedragon web
> server. im confuse with vacuum steps, normally i use

You really should look at updating to a later version of pgsql, and
ubuntu if you can manage it.  pgsql 8.2 is EOL.

> *psql database_name
> database_name=# vacuum;/vacuum full/ vacuum full analyst*
> to perform basic maintenance to the system. but the user sometimes was feel
> very slow while accessing using browser, information search will hang over
> there.

You shouldn't normally need to do a vacuum and especially vacuum full
unless autovacuum isn't doing it's job correctly.  Note that earlier
versions of autovacuum were single threaded and tuned pretty
conservatively so that you could get into some trouble with table and
index bloat.  Do you have any evidence that autovacuum isn't working
on this machine?


Re: Database in psql

От
teyking2003
Дата:
for the upgrade of psql version, do we need to reconfigure all again?



--
View this message in context: http://postgresql.1045698.n5.nabble.com/Database-in-psql-tp5728767p5729131.html
Sent from the PostgreSQL - admin mailing list archive at Nabble.com.


Re: Database in psql

От
Craig Ringer
Дата:
On 10/20/2012 08:18 AM, teyking2003 wrote:
> for the upgrade of psql version, do we need to reconfigure all again?

The manual explains the details:

  http://www.postgresql.org/docs/current/static/upgrading.html

You should also read the release notes for the major versions (8.3, 8.4,
9.0, 9.1 and 9.2) between your version and the target for your upgrade.
Read the compatibility notes.

You will need to be particularly careful of:

- The move to standard_conforming_strings
- The change of bytea_format
- The removal of implicit casts to text
- Upgrading contrib modules to extensions

so you will need to do some application testing.

--
Craig Ringer


Re: Database in psql

От
teyking2003
Дата:
We might not consider upgrade our existing psql 8.2.5 to the latest one
because of this psql version is bundle with our web application. and the
whole system design by the vendor. we only can perform maintenance steps to
that database, scare any side effect to our web application.

any place to check the autovacuum schedule in our existing system?



--
View this message in context: http://postgresql.1045698.n5.nabble.com/Database-in-psql-tp5728767p5729477.html
Sent from the PostgreSQL - admin mailing list archive at Nabble.com.


Re: Database in psql

От
"Albe Laurenz"
Дата:
teyking2003 wrote:
> We might not consider upgrade our existing psql 8.2.5 to the latest
one
> because of this psql version is bundle with our web application. and
the
> whole system design by the vendor. we only can perform maintenance
steps to
> that database, scare any side effect to our web application.

If your vendor does not support anything later than 8.2,
you should change vendor (and product), if you can.

Is there no later version of the product that supports
a supported version of PostgreSQL?

> any place to check the autovacuum schedule in our existing system?

I am not sure what you mean -- if enabled, autovacuum is scheduled
according to the parameters mentioned in
http://www.postgresql.org/docs/8.2/static/routine-vacuuming.html#AUTOVAC
UUM

If you want to know when a table was last vacuumed, there's
the catalog table pg_stat_all_tables.

Yours,
Laurenz Albe