Обсуждение: Backups
I want a script to backup all databases table by table in different .sql files. How to do this ? Also since it is a script, there has to be some role having read access to all tables on all databases. How to grant that ? -- Nilesh Govindarajan Site & Server Adminstrator www.itech7.com
Nilesh Govindarajan wrote: > I want a script to backup all databases table by table in different > .sql files. My first question would be whether they all need to be from the same point in time? Is it a requirement that if they were all restored they would provide a database with referential integrity, etc.? Or is it OK if each table represents a different moment in time? Either way, you probably want pg_dump. If you don't care about having these separate files to collectively provide a consistent image of a moment in time, or you can prevent any modifications while you are running a set of backups, you could use pg_dump with the -t option. Otherwise you probably need to use pg_dump (or pg_dumpall) and parse the output to split it into the required files. Or you might be able to do pg_dump to the custom format and extract each table into a separate file. > Also since it is a script, there has to be some role having read > access to all tables on all databases. How to grant that ? If you don't want to use the database superuser and don't already have some other role with the appropriate rights, you will have to grant them. It's hard to provide details without more information about what's already there. -Kevin
On Sat, Feb 20, 2010 at 10:11 AM, Nilesh Govindarajan <lists@itech7.com> wrote: > I want a script to backup all databases table by table in different .sql > files. Why? It may be that pg_dump / pg_restore already provide the functionality you are looking for. > How to do this ? pg_dump -t tablename dbname rinse repeat > Also since it is a script, there has to be some role having read access to > all tables on all databases. How to grant that ? Can they just be a superuser? Or can you grant them the role that has ownership of the tables?
On 02/20/2010 11:14 PM, Kevin Grittner wrote: > Nilesh Govindarajan wrote: > >> I want a script to backup all databases table by table in different >> .sql files. > > My first question would be whether they all need to be from the same > point in time? Is it a requirement that if they were all restored > they would provide a database with referential integrity, etc.? Or > is it OK if each table represents a different moment in time? > > Either way, you probably want pg_dump. If you don't care about > having these separate files to collectively provide a consistent > image of a moment in time, or you can prevent any modifications while > you are running a set of backups, you could use pg_dump with the -t > option. Otherwise you probably need to use pg_dump (or pg_dumpall) > and parse the output to split it into the required files. Or you > might be able to do pg_dump to the custom format and extract each > table into a separate file. > >> Also since it is a script, there has to be some role having read >> access to all tables on all databases. How to grant that ? > > If you don't want to use the database superuser and don't already > have some other role with the appropriate rights, you will have to > grant them. It's hard to provide details without more information > about what's already there. > > -Kevin That is what exactly. I can prevent the changes to the database when the backup is going on, but how do I grant the permissions ? I don't want to key in the password for the superuser in the script. -- Nilesh Govindarajan Site & Server Adminstrator www.itech7.com
On 02/20/2010 11:23 PM, Scott Marlowe wrote: > On Sat, Feb 20, 2010 at 10:11 AM, Nilesh Govindarajan<lists@itech7.com> wrote: >> I want a script to backup all databases table by table in different .sql >> files. > > Why? It may be that pg_dump / pg_restore already provide the > functionality you are looking for. > >> How to do this ? > > pg_dump -t tablename dbname > > rinse repeat > >> Also since it is a script, there has to be some role having read access to >> all tables on all databases. How to grant that ? > > Can they just be a superuser? Or can you grant them the role that has > ownership of the tables? I don't feel safe to key in the root password in the script. I was previously using MySQL, in which I had a user called backup without password having SELECT access on all tables and databases. I need something similar in PgSQL. A possible solution seems to me is to use the trust auth for a user in pg_hba.conf, but if the user is given superuser status, then it will have write access to all databases, which is extremely dangerous. -- Nilesh Govindarajan Site & Server Adminstrator www.itech7.com
Nilesh Govindarajan wrote: > I can prevent the changes to the database when the > backup is going on, but how do I grant the permissions ? I don't > want to key in the password for the superuser in the script. There are so many ways to deal with that that it's hard to make a suggestion without knowing more. You could just use CREATE USER and GRANT to set things up, you could use trust authentication in the pg_hba.conf to grant a database superuser permission from a secure machine, or you could use a pgpass file. (Check the documentation for details.) If I got creative I could probably come up with more, but I'm shooting in the dark here. -Kevin
On Sat, Feb 20, 2010 at 6:57 PM, Nilesh Govindarajan <lists@itech7.com> wrote: > On 02/20/2010 11:23 PM, Scott Marlowe wrote: >> >> On Sat, Feb 20, 2010 at 10:11 AM, Nilesh Govindarajan<lists@itech7.com> >> wrote: >>> >>> I want a script to backup all databases table by table in different .sql >>> files. >> >> Why? It may be that pg_dump / pg_restore already provide the >> functionality you are looking for. >> >>> How to do this ? >> >> pg_dump -t tablename dbname >> >> rinse repeat >> >>> Also since it is a script, there has to be some role having read access >>> to >>> all tables on all databases. How to grant that ? >> >> Can they just be a superuser? Or can you grant them the role that has >> ownership of the tables? > > I don't feel safe to key in the root password in the script. Why would you need to have a password in your script? Just use .pgpass file for whichever unix account is going to connect, and then grant that user the SQL privileges to dump the db. I think you need to do more reading of the docs on user accounts etc under pgsql. > I was previously using MySQL, in which I had a user called backup without > password having SELECT access on all tables and databases. There are lots of ways to attack this, some of which do not require a password to be stored anywhere. At no time should a unix root account be involved. > I need something similar in PgSQL. A possible solution seems to me is to use > the trust auth for a user in pg_hba.conf, but if the user is given superuser > status, then it will have write access to all databases, which is extremely > dangerous. trust auth works but has the issues you mention. the .pgpass file is the easiest way to pass a password without including it in a script or anything. http://www.postgresql.org/docs/8.3/interactive/libpq-pgpass.html Note that the user account in unix doesn't have to map directly to the same name in postgresql.