[ADMIN] Backup Strategies?

[Joshua Kramer]

Hello All,

What strategies are people using for automated, script-based backup of 
databases?  There are a few I can think of:


1. Create a "db_backup" unix user and a "db_backup" pgsql user.  Grant 
READ access to all objects on all databases for the "db_backup" pgsql 
user.  Create a .pgpass file in the home directory of the "db_backup" unix 
user.  Backup as needed with a script run as the "db_backup" unix user.


2. Create a "db_backup" unix user and repeat above, except using the 
"postgres" db user.


Thanks,
-Josh


---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

Re: [ADMIN] Backup Strategies?

[Joshua Kramer]

Thanks for the hint, Shoaib.  My question - and I should have phrased this 
more carefully - related to the security of the various backup tactics. 
Is it more secure to have a "backup user" with read permissions on all 
databases, or is it more secure to have a dedicated Unix backup user - 
with the .pgpass file in the home directory and all.  If someone can, by 
way of cracking, get in to the account of the backup Unix user, then the 
postgres user's database account is also surrendered.  If I have a 
Postgres backup user (with read only permissions on all DB's), then even 
if someone got into the unix account of the backup user, all they could do 
is read DB data (versus delete or write over stuff).


Essentially it's the diffrenece between being *really* secure and *really 
really* secure.


On Sat, 3 Feb 2007, Shoaib Mir wrote:


A cron job can always do the job using pg_dump/pg_dumpall, in case you need
the incremental backup you can opt for PITR WAL based archive logs...

--
Shoaib Mir
EnterpriseDB (www.enterprisedb.com)

On 2/3/07, Joshua Kramer <[EMAIL PROTECTED]> wrote:



Hello All,

What strategies are people using for automated, script-based backup of
databases?  There are a few I can think of:

1. Create a "db_backup" unix user and a "db_backup" pgsql user.  Grant
READ access to all objects on all databases for the "db_backup" pgsql
user.  Create a .pgpass file in the home directory of the "db_backup" unix
user.  Backup as needed with a script run as the "db_backup" unix user.

2. Create a "db_backup" unix user and repeat above, except using the
"postgres" db user.

Thanks,
-Josh


---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster





---(end of broadcast)---
TIP 7: You can help support the PostgreSQL project by donating at

   http://www.postgresql.org/about/donate

Re: [ADMIN] Isolated databases or instances

[Joshua Kramer]

If you really need three discrete instances, you might want to look at
using vmware to set up three separate virtual machines, each with their
own IPs etc... on that one box.


This was my thought; depending on how much RAM you have in each blade, you 
could have two virtual servers on each blade, one server being HTTP and 
one being Postgres.


I'm not sure how big your application is, but if it's not the next SAP 
then you might be able to get away with as little as 1G of RAM per blade 
(on the dev and QA instances anyway), if you virtualize Linux servers 
using XEN.  That's the way I'd do it.


Cheers,
-J


---(end of broadcast)---
TIP 4: Have you searched our list archives?

  http://archives.postgresql.org

Re: [ADMIN] which distro, or to build

[Joshua Kramer]

David,


I'm getting really mad at RHEL 4. They do not package a recent
postgres in the base distro, and only put 8.1.x in a disk call
lamp-beta... So now I wonder what a 'recommended' way of running
postgresql is -- meaning, any linux distro will do, just build it --


Why not just download the RHEL4 packages from the Postgres mirrors?  PG 
runs fine for me in that manner, on CentOS-4 (a RHEL rebuild).


Cheers,
-J


---(end of broadcast)---
TIP 4: Have you searched our list archives?

  http://archives.postgresql.org

Re: [ADMIN] which distro, or to build

[Joshua Kramer]

David,

I'm not sure what dependencies the RH-provided PG RPM's have, but I've had 
no problems whatsoever installing a series of PG-sourced RPM's from 8.1 on 
up to 8.2.3, on CentOS 4 (which is binary-compatible with RHEL4).


Actually, there is one minor glitch; some apps that use PG require an 
older version of libpq, but the Postgres website provides a "compat 
postgreql libs" for this purpose.


In any case, if you are using the PG-supplied RPM's and you know you're 
installing everything, simply run RPM like so:


rpm -i --nodeps postgres-whatever.rpm

I usually use OS-vendor supplied RPMS for things like Apache, except in 
cases where the project-supplied RPM's have additional features over and 
above the RHEL/CentOS RPM's.  Things like Postgres and Python fall under 
this category.


Cheers,
-Josh

On Tue, 20 Mar 2007, David Bear wrote:


On Tue, Mar 20, 2007 at 09:06:35PM -0400, Joshua Kramer wrote:


David,


I'm getting really mad at RHEL 4. They do not package a recent
postgres in the base distro, and only put 8.1.x in a disk call
lamp-beta... So now I wonder what a 'recommended' way of running
postgresql is -- meaning, any linux distro will do, just build it --


Why not just download the RHEL4 packages from the Postgres mirrors?  PG
runs fine for me in that manner, on CentOS-4 (a RHEL rebuild).


Thought of that and then wondered if I would face the same dependancy
issues that using the rpms from redhat gave me. Do you know if redhat
just redistributed the pg rpms? Or, do these rpms actually include
depndancy resolution? For example, trying to install the rpm from
redhat I get a failed dependance for libpq.so.4 --- but I have no idea
what package redhat would have put libpq in... and so it goes. Where
should I spend time? Tracking down dependancies? Or finding a better
host operating system -- at least one packaged with things the pg
needs.



Cheers,
-J


--
David Bear
phone:  602-496-0424
fax:602-496-0955
College of Public Programs/ASU
University Center Rm 622
411 N Central
Phoenix, AZ 85007-0685
"Beware the IP portfolio, everyone will be suspect of trespassing"




---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
  choose an index scan if your joining column's datatypes do not
  match

Re: [ADMIN] [pgadmin-support] Motivations for PostgreSQL

[Joshua Kramer]

Aaron,

You might want to probe a little further.  Even though you know their 
reason for wanting SQL Server - "cause it's what they know" - ask them 
specifically what advantages SQL Server will give them over their current 
setup.  If you know what *they think* their objections are, then you are 
better equipped to handle those objections on their terms, which is what 
really matters.


Tell them that you're more than happy to install and use SQL server, if 
they pay for the licensing for Windows, licensing for SQL Server, 
additional hardware, training, and application migration that will *ensure 
that you can continue to provide your excellent, non-interrupted service* 
that you currently provide using PostgreSQL.  And then let them know 
exactly how much that is, in the tens of thousands of US dollars.


Cheers,
-J

On Tue, 10 Apr 2007, Aaron Bono wrote:


The problem is, this isn't a logical decision they are making.  They are
only comfortable with Micro$oft and when I mention a database they haven't
heard of before, they cringe even though I have proven it's ability to serve
all their needs for the last year.

What it comes down to is that I need to SELL them on the idea of using
PostgreSQL or they may just take their business elsewhere just because I
don't use Microsoft.  I think they may actually rather pay tens of thousands
of dollars more for that stamp from Borg Gates.

Are there good articles that compare the databases or talk about PostgreSQL
- I need to site good references.

Thanks,
Aaron

On 4/10/07, Ezequias Rodrigues da Rocha <[EMAIL PROTECTED]> wrote:


Tranfer the responsability (time, cost, performance, training) to your
customer.

Another thing you could ask them to prove why to use the other plataform.

There isn't many differencies about SGDBs performance for me. I prove it
by myself and the results where better than Oracle in some cases.

I hope you could make, at least, them think about it.

--
Ezequias Rodrigues da Rocha
http://ezequiasrocha.blogspot.com/
use Mozilla Firefox:http://br.mozdev.org/firefox/

- Mensagem original 
Assunto: [pgadmin-support] Motivations for PostgreSQL

I have a customer who is beginning to give more attention to the
applications we host for them.  They recently asked what database we use 
and

I told them PostgreSQL.  They had never heard of it and really like
Micro$oft SQL Server.  I explained to them the added cost and how 
PostgreSQL

is a solid database but I need more to convince them.

What are some good resources online that discuss PostgreSQL and how it
compares to the "more popular" databases?  While it really should be none 
of

my customers' business what database I use (I am an application service
provider), they still make a big deal out of it.

Thanks!







--
==
 Aaron Bono
 Aranya Software Technologies, Inc.
 http://www.aranya.com
 http://codeelixir.com
==



---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [ADMIN]

[Joshua Kramer]

I already found that it's possible to store passwords in the file pgpass.conf 
in the %APPDATA% directory. But that file isn't stored in that location. In 
fact it doesn't exist in my disk. I already checked the "Store password"


Filipe,

You need to create that file yourself.  Using the format you found in the 
documentation, you can put your login information in the .pgpass file in 
the user's home directory, or under windows it's under
"%APPDATA%\postgresql\pgpass.conf (where %APPDATA% refers to the 
Application Data subdirectory in the user's profile)." [1]


Hope that helps!


Cheers,
-J

[1] http://developer.postgresql.org/pgdocs/postgres/libpq-pgpass.html


---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

  http://www.postgresql.org/docs/faq

Re: [ADMIN] PostgreSQL Training?

[Joshua Kramer]

Also note that RedHat does PostgreSQL training:

https://www.redhat.com/training/developer/courses/rdb147.html

Note, however, that this is "RedHat Database" training.  "RedHat Database" 
was a rebranded PostgreSQL 7.3.4 introduced back around 2004; so I'm not 
sure if this training would include anything that is "Postgres Generic" 
or any features introduced after 7.3.4.


Cheers,
-J


---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

Re: [ADMIN] Deploying Postgresql Along My Windows App

[Joshua Kramer]

Hello Valter,

I am doing something similar with the Windows installer for the PostBooks 
project.  Basically, what I do is this:


1. Use the Postgres windows installer to install 8.2.7
2. Import all the application's databases.
3. Using the NSIS Installer Generation app:
	1. Include all files under c:\program files\postgresql... in my 
project;
	2. Use NSIS utilities to install the PostgreSQL service in a 
custom manner.  I do this on a non-standard port so that if the user 
already has PG, it does not clobber the existing install.


I don't have a lot of time at the moment, but later today I can go into 
details if you like.  In the meantime, check out:


http://nsis.sourceforge.net/Main_Page
http://hmne.sourceforge.net/
http://www.xtuple.org

Cheers,
-J

On Mon, 14 Apr 2008, Valter Nogueira wrote:


Date: Mon, 14 Apr 2008 23:38:02 -0300
From: Valter Nogueira <[EMAIL PROTECTED]>
To: pgsql-admin@postgresql.org
Subject: [ADMIN] Deploying Postgresql Along My Windows App

I am developing a wxPython app which can run, like Postgresql, on Linux
and Windows boxes.
 
Linux users are far more specialized than Windows one - and Postgres
comes along with major distros.
 
Windows users don't want (or are unable to) install Postgresql by
themselves, so I was wondering if there is a way to deploy (or embed) it
along my app.
 
I would like to decompress a non-installer bin version, setup the initial
catalog and run the server without users intervention.
 
Can anyone help me?
 
Thanks,
 
Valter
 
 




--

-
http://www.globalherald.net/jb01
GlobalHerald.NET, the Smarter Social Network! (tm)
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin