backing up my G2 Postgres database - having issues

patrickthickey

Joined: 2004-08-22
Posts: 164

Posted: Thu, 2005-12-08 15:55

I would appreciate some pointers on PostGres backup syntax as I am really unclear. My gallery2 installation uses a database named gallery2 (how clever is that?). I am logged in as root in the directory into which I wish to do the dump. How can root not have permissions?
I fully admit to not being a database person but the command to do this is very straight forward. Am I missing a path of some type?

My gallery is sufficiently large now to require a solid backup.

Thank you in advance.

[root@yossarian PostGres_backup]# pg_dump gallery2 -f 12805
pg_dump: attempt to lock table "g2_schema" failed: ERROR: permission denied for relation g2_schema
[root@yossarian PostGres_backup]#

Gallery version = 2.0.2 core 1.0.0.2
PHP version = 4.3.9 apache2handler
Webserver = Apache/2.0.52 (Red Hat)
Database = postgres7 (appended 7.4.7)
Toolkits = ArchiveUpload, Exif, Dcraw, Ffmpeg, ImageMagick, NetPBM, Gd
Operating system = Linux yossarian.idyll.com 2.6.9-11.ELsmp #1 SMP Fri May 20 18:25:30 EDT 2005 x86_64

mdev

Joined: 2005-12-08
Posts: 17

Posted: Fri, 2005-12-09 14:15

Because postgres != operating system.

You need to run the backup as the user that created the database. Chances are that's the same as the webserver. If all else fails, try the user postgres runs under, which is the user that owns /path/to/pgsql/data.

See -U argument in pg_ctl, or su(8) specifically the -c and -fm arguments.

patrickthickey

Joined: 2004-08-22
Posts: 164

Posted: Fri, 2005-12-09 18:24

mdev - thanks for the reply.

I read your note and SSL'd in, su'd to root and issued this command from inside the output directory...

pg_dump gallery2 > 12905

and the thing backed itself up. In other words, using any options seems to make it croak. I had previously tried a couple of options, never just a straight pg_dump. Your reference to the documentation resulted in my reading it (a good thing) and then throwing caution to the wind and just using the command without any arguments. How very strange.

But, I accept it!

Thank you again. Are you a postgresql person by trade?

Right now, if you talk to people
who try to run real-world systems,
it is a struggle against entropy.

mdev

Joined: 2005-12-08
Posts: 17

Posted: Fri, 2005-12-09 21:29

Nope, but tangoed with a few times.

They break backwards compatibility too fast for me though, requiring full pg_dumps and restores. I only use it if I need complex relations and where views are a must, like with financial programs. This is also where postgres can be noticeably faster, because you'd have to code too many tricks with mysql.

Lack of a stable open-source replication feature also has been my problem and pgreplication at gborg seems to have died as well. I see there's Slony now though, might check it out.

patrickthickey Joined: 2004-08-22 Posts: 164	Posted: Thu, 2005-12-08 15:55
	I would appreciate some pointers on PostGres backup syntax as I am really unclear. My gallery2 installation uses a database named gallery2 (how clever is that?). I am logged in as root in the directory into which I wish to do the dump. How can root not have permissions? I fully admit to not being a database person but the command to do this is very straight forward. Am I missing a path of some type? My gallery is sufficiently large now to require a solid backup. Thank you in advance. [root@yossarian PostGres_backup]# pg_dump gallery2 -f 12805 pg_dump: attempt to lock table "g2_schema" failed: ERROR: permission denied for relation g2_schema [root@yossarian PostGres_backup]# Gallery version = 2.0.2 core 1.0.0.2 PHP version = 4.3.9 apache2handler Webserver = Apache/2.0.52 (Red Hat) Database = postgres7 (appended 7.4.7) Toolkits = ArchiveUpload, Exif, Dcraw, Ffmpeg, ImageMagick, NetPBM, Gd Operating system = Linux yossarian.idyll.com 2.6.9-11.ELsmp #1 SMP Fri May 20 18:25:30 EDT 2005 x86_64
	XUser login Username: * Password: * Create new account Request new password
mdev Joined: 2005-12-08 Posts: 17	Posted: Fri, 2005-12-09 14:15
	Because postgres != operating system. You need to run the backup as the user that created the database. Chances are that's the same as the webserver. If all else fails, try the user postgres runs under, which is the user that owns /path/to/pgsql/data. See -U argument in pg_ctl, or su(8) specifically the -c and -fm arguments.

patrickthickey Joined: 2004-08-22 Posts: 164	Posted: Fri, 2005-12-09 18:24
	mdev - thanks for the reply. I read your note and SSL'd in, su'd to root and issued this command from inside the output directory... pg_dump gallery2 > 12905 and the thing backed itself up. In other words, using any options seems to make it croak. I had previously tried a couple of options, never just a straight pg_dump. Your reference to the documentation resulted in my reading it (a good thing) and then throwing caution to the wind and just using the command without any arguments. How very strange. But, I accept it! Thank you again. Are you a postgresql person by trade? Right now, if you talk to people who try to run real-world systems, it is a struggle against entropy.

mdev Joined: 2005-12-08 Posts: 17	Posted: Fri, 2005-12-09 21:29
	Nope, but tangoed with a few times. They break backwards compatibility too fast for me though, requiring full pg_dumps and restores. I only use it if I need complex relations and where views are a must, like with financial programs. This is also where postgres can be noticeably faster, because you'd have to code too many tricks with mysql. Lack of a stable open-source replication feature also has been my problem and pgreplication at gborg seems to have died as well. I see there's Slony now though, might check it out.

backing up my G2 Postgres database - having issues

XUser login