Postgresql utilities

Rails
1

I’m thinking that it would be very attractive to have some methods in
place that would do things like pg_dump on a per table basis and do
things like vacuum but I would want standard out directed to the screen
so I could see the effects.

I haven’t found any plugins that do things like that - does anyone have
any direction/suggestions?

Craig

2

On Thu, 2007-03-08 at 19:16 -0700, Craig W. wrote:

I’m thinking that it would be very attractive to have some methods in
place that would do things like pg_dump on a per table basis and do
things like vacuum but I would want standard out directed to the screen
so I could see the effects.

I haven’t found any plugins that do things like that - does anyone have
any direction/suggestions?

no one? Am I the first to consider this or is this not ruby material?

Craig

3

On Friday 09 March 2007, Craig W. wrote:

I’m thinking that it would be very attractive to have some methods in
place that would do things like pg_dump on a per table basis and do
things like vacuum but I would want standard out directed to the
screen so I could see the effects.

I haven’t found any plugins that do things like that - does anyone
have any direction/suggestions?

Well, why would you need it? I’m using PostgreSQL myself and I don’t
have a use for this functionality. What’s the purpose of dumping a
single table? For backups, I dump the entire database. For export, I
use CSV or XML. I’m not saying that you’re trying to do something
wrong, but simply that I don’t understand what it is you want to
achieve.

Regarding vacuum(ing), I think this is not a task that Rails should care
about. This is a task that your PostgreSQL installation should handle
with the help of cron jobs. As a matter of fact, that’s just how it
works on Debian. Below is an excerpt
from /etc/cron.d/postgresql-common.

Michael

Run VACUUM ANALYSE on all databases every 5 hours if pg_autovacuum is

not

running

2 0,5,10,15,20 * * 1-6 root if [ -x /usr/sbin/pg_maintenance ];
then /usr/sbin/pg_maintenan
ce --analyze >/dev/null; fi

On Sunday you may wish to run a VACUUM FULL ANALYSE as well

If you do not run a 24/7 site, you may want to uncomment the next line

so as to do a regular VACUUM FULL. If you need 24/7 connectivity,

save

VACUUM FULL for when you think you really need it.

10 3 * * Sun root if [ -x /usr/sbin/pg_maintenance ];
then /usr/sbin/pg_maintenance --full
–analyze >/dev/null; fi

–
Michael S.
mailto:[email protected]
http://www.schuerig.de/michael/

4

On Sat, 2007-03-10 at 01:57 -0800, Jeremy K. wrote:

On 3/10/07, Michael S. [email protected] wrote:

Regarding vacuum(ing), I think this is not a task that Rails should care
about. This is a task that your PostgreSQL installation should handle

Plus, PostgreSQL 8.x autovacuums.

I know, but I’m using CentOS 4.4 and it is still PostgreSQL 7.4.x

Craig

5

On Sat, 2007-03-10 at 10:24 +0100, Michael S. wrote:

have a use for this functionality. What’s the purpose of dumping a
single table? For backups, I dump the entire database. For export, I
use CSV or XML. I’m not saying that you’re trying to do something
wrong, but simply that I don’t understand what it is you want to
achieve.

I do a full dump every night but I’m thinking that it would be nice to
have a table by table backup on demand because even though I try to
thoroughly test every thing before I migrate code into production, I do
have occasions where I have to tweak production code and I was thinking
that these types of on demand backups would be nice without having to
fire up something else (like pgadmin or phppgadmin).

I have already created a mini-controller for utilities to do things like
expire cached pages and was simply thinking of adding some postgresql
functionality.

Run VACUUM ANALYSE on all databases every 5 hours if pg_autovacuum is

VACUUM FULL for when you think you really need it.

10 3 * * Sun root if [ -x /usr/sbin/pg_maintenance ];
then /usr/sbin/pg_maintenance --full
–analyze >/dev/null; fi

thanks

Craig

6

On 3/10/07, Michael S. [email protected] wrote:

Regarding vacuum(ing), I think this is not a task that Rails should care
about. This is a task that your PostgreSQL installation should handle

Plus, PostgreSQL 8.x autovacuums.

jeremy

7

On Mar 10, 2007, at 8:27 AM, Craig W. wrote:

Plus, PostgreSQL 8.x autovacuums.

I know, but I’m using CentOS 4.4 and it is still PostgreSQL 7.4.x

upgrade. Postgres 8.x is much faster.

-pete

8

Jumping on the “upgrade to PostgreSQL 8” bandwagon:

$ pg_dump --help |grep table
-t, --table=TABLE dump the named table only
$ pg_dump --version
pg_dump (PostgreSQL) 8.1.8

-molecule

9

Also, there’s a plugin that does this on a per-Model, db-agnostic
basis in Rails:

Dump or Slurp YAML Reference Data (and Fixtures)
http://nubyonrails.com/articles/2005/12/27/dump-or-slurp-yaml-reference-data

It’s very handy for dumping and loading data in Rails. It plugs into
Rake now via “rake db:data:dump” and “rake db:data:load”.

cheers,

10

On Sat, 2007-03-10 at 16:28 -0800, [email protected] wrote:

Also, there’s a plugin that does this on a per-Model, db-agnostic
basis in Rails:

Dump or Slurp YAML Reference Data (and Fixtures)
http://nubyonrails.com/articles/2005/12/27/dump-or-slurp-yaml-reference-data

It’s very handy for dumping and loading data in Rails. It plugs into
Rake now via “rake db:data:dump” and “rake db:data:load”.

that might be good enough for my purposes…thanks

Craig