CRON Jobs and Backups

When I try to remove a database with phppgadmin, I get the following error, "ERROR: cannot drop the currently open database". Any insight would be appreciated.

Sean

Hi,

On 6/1/05, Sean Montague <couloir007(at)yahoo(dot)com> wrote:
> When I try to remove a database with phppgadmin, I get the following
> error, "ERROR: cannot drop the currently open database". Any insight
> would be appreciated.

You cannot cut the branch you're on. :)

Try to connect template1 (or template0) then remove that
_not_connected_ database. AFAIC, you couldn't remove a database
serving at the moment.

Regards.

On Jun 1, 2005, at 3:05 PM, Sean Montague wrote:

> When I try to remove a database with phppgadmin, I get the following
> error, "ERROR: cannot drop the currently open database". Any insight
> would be appreciated.

Seems like a bug to me. I thought the "Logout" link would help, but I
could not find a way to make it work.

John DeSoi, Ph.D.
http://pgedit.com/
Power Tools for PostgreSQL

Hi,

On 6/6/05, John DeSoi <desoi(at)pgedit(dot)com> wrote:
> On Jun 1, 2005, at 3:05 PM, Sean Montague wrote:
> > When I try to remove a database with phppgadmin, I get the following
> > error, "ERROR: cannot drop the currently open database". Any insight
> > would be appreciated.
>
> Seems like a bug to me. I thought the "Logout" link would help, but I
> could not find a way to make it work.

Oops. You seem to be right. (I can drop it by opening a SQL query
window from phpPgAdmin and executing "DROP DATABASE".) It could be a
bug. (I tried to dig a little bit in source code but, it requires an
experience on the program design. So just messed up.)

Regards.

hi all,

i've googled and searche dmy pgsql books and i can't
quite get a confident grasp on cron jobs.

as i understand it... the following should enable me
to backup via cron:

PGUSER=postgres
PGPASSWORD=password
export PGUSER PGPASSWORD
pg_dump databasename | gzip > databasename.bak.gz

my interfaces is cpanel and it only gives me one line
to type this in... can i replace the returns with a
space and will this still work?

eg, PGUSER=postgres PGPASSWORD=password export...

will databasename.bak.gz be stored in the same file as
my database?

how can i store the file on my local computer?

i finally have some data in my db and i scared of
losing it. *real* scared!

tia...

__________________________________
Discover Yahoo!
Have fun online with music videos, cool games, IM and more. Check it out!
http://discover.yahoo.com/online.html

> hi all,
>
> i've googled and searche dmy pgsql books and i can't
> quite get a confident grasp on cron jobs.
>
> as i understand it... the following should enable me
> to backup via cron:
>
> PGUSER=postgres
> PGPASSWORD=password
> export PGUSER PGPASSWORD
> pg_dump databasename | gzip > databasename.bak.gz
>
> my interfaces is cpanel and it only gives me one line
> to type this in... can i replace the returns with a
> space and will this still work?
>
> eg, PGUSER=postgres PGPASSWORD=password export...

Don't know about that... read up on /bin/sh to see how it handles things
like that.

If cpanel let's you upload files, you could put all of the above in a
'backup-pgsql.sh" file on your server then in your cron job put:

/bin/sh backup-pgsql.sh

> will databasename.bak.gz be stored in the same file as
> my database?

probably not. If I were you, I'd put the full path of where you want the
file to be stored.

> how can i store the file on my local computer?

Mail it to yourself, or download it via FTP/HTTP...

-philip

am 06.06.2005, um 10:07:39 -0700 mailte operationsengineer1(at)yahoo(dot)com folgendes:
> PGUSER=postgres
> PGPASSWORD=password
> export PGUSER PGPASSWORD
> pg_dump databasename | gzip > databasename.bak.gz
>
> my interfaces is cpanel and it only gives me one line
> to type this in... can i replace the returns with a
> space and will this still work?
>
> eg, PGUSER=postgres PGPASSWORD=password export...
>
> will databasename.bak.gz be stored in the same file as
> my database?

No, the database will be stored in 'databasename.bak.gz'.

>
> how can i store the file on my local computer?

Call pg_dump from your computer, but with -h - parameter.

> i finally have some data in my db and i scared of
> losing it. *real* scared!

?

I used today:

pg_dump -h 'old_server' -n 'schema' -U 'user' 'db' | psql -U 'user' 'new_deb'

(just as a brain-dump, no guarantee)

to transfer a Database from a old to a new Server.

Andreas
--
Andreas Kretschmer (Kontakt: siehe Header)
Heynitz: 035242/47212, D1: 0160/7141639
GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net
=== Schollglas Unternehmensgruppe ===

<operationsengineer1(at)yahoo(dot)com> writes:
> as i understand it... the following should enable me
> to backup via cron:

> PGUSER=postgres
> PGPASSWORD=password
> export PGUSER PGPASSWORD
> pg_dump databasename | gzip > databasename.bak.gz

This is probably a pretty bad way to handle the password --- on many
Unix variants, anyone can see the list of environment variables that is
passed down to the pg_dump process. It'd be far safer to put the
password in a ~/.pgpass file owned by the Unix user that runs the cron
job.

> my interfaces is cpanel and it only gives me one line
> to type this in... can i replace the returns with a
> space and will this still work?

Get a better user interface ;-). Or put the whole mess in a script file
and invoke the script file from your cron job.

regards, tom lane

Hi,

Here's simple instructions and sample for sftp and cron (linux), which should
give you an idea how it works. You can then create your own scripts for the
actual job.

http://tennis.ecs.umass.edu/~czou/linux/backupSSH.html

BR,

Aarni

On Monday 06 June 2005 20:07, you wrote:
> hi all,
>
> i've googled and searche dmy pgsql books and i can't
> quite get a confident grasp on cron jobs.
>
> as i understand it... the following should enable me
> to backup via cron:
>
> PGUSER=postgres
> PGPASSWORD=password
> export PGUSER PGPASSWORD
> pg_dump databasename | gzip > databasename.bak.gz
>
> my interfaces is cpanel and it only gives me one line
> to type this in... can i replace the returns with a
> space and will this still work?
>
> eg, PGUSER=postgres PGPASSWORD=password export...
>
> will databasename.bak.gz be stored in the same file as
> my database?
>
> how can i store the file on my local computer?
>
> i finally have some data in my db and i scared of
> losing it. *real* scared!
>
> tia...
>
>
>
> __________________________________
> Discover Yahoo!
> Have fun online with music videos, cool games, IM and more. Check it out!
> http://discover.yahoo.com/online.html
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faq

--
Aarni Ruuhimäki
Megative Tmi
Pääsintie 26
45100 Kouvola
Finland
+358-5-3755035
+358-50-4910037

www.kymi.com | cfm.kymi.com
--------------
This is a bugfree broadcast to you
from **Kmail**
on **Fedora Core 2** linux system
--------------
Linux is like a wigwam - no windows, no gates and a free apache inside.

Depends. If you have only one database, I suppose tar/zip everything
together might be tempting. I host several domains, each with their own
databases. I use pg_dump for data backup; use the original install package
as backup to binaries; and make copies of config/startup.

Frank

At 10:11 AM 6/8/05, Todd Lewis wrote:

>I've started looking at backup options myself. Which is better, tar
>zipping the binaries and data files together, separately, or just running
>pg_dump.
>
>Aarni Ruuhimäki wrote:
>
>>Hi,
>>
>>Here's simple instructions and sample for sftp and cron (linux), which
>>should give you an idea how it works. You can then create your own
>>scripts for the actual job.
>>
>>http://tennis.ecs.umass.edu/~czou/linux/backupSSH.html
>>
>>BR,
>>
>>Aarni
>>
>>
>>On Monday 06 June 2005 20:07, you wrote:
>>
>>
>>>hi all,
>>>
>>>i've googled and searche dmy pgsql books and i can't
>>>quite get a confident grasp on cron jobs.
>>>
>>>as i understand it... the following should enable me
>>>to backup via cron:
>>>
>>>PGUSER=postgres
>>>PGPASSWORD=password
>>>export PGUSER PGPASSWORD
>>>pg_dump databasename | gzip > databasename.bak.gz
>>>
>>>my interfaces is cpanel and it only gives me one line
>>>to type this in... can i replace the returns with a
>>>space and will this still work?
>>>
>>>eg, PGUSER=postgres PGPASSWORD=password export...
>>>
>>>will databasename.bak.gz be stored in the same file as
>>>my database?
>>>
>>>how can i store the file on my local computer?
>>>
>>>i finally have some data in my db and i scared of
>>>losing it. *real* scared!
>>>
>>>tia...
>>>
>>>
>>>
>>>__________________________________
>>>Discover Yahoo!
>>>Have fun online with music videos, cool games, IM and more. Check it out!
>>>http://discover.yahoo.com/online.html
>>>
>>>---------------------------(end of broadcast)---------------------------
>>>TIP 5: Have you checked our extensive FAQ?
>>>
>>> http://www.postgresql.org/docs/faq
>>>
>>
>>
>
>
>---------------------------(end of broadcast)---------------------------
>TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faq

I've started looking at backup options myself. Which is better, tar
zipping the binaries and data files together, separately, or just
running pg_dump.

Aarni Ruuhimäki wrote:

>Hi,
>
>Here's simple instructions and sample for sftp and cron (linux), which should
>give you an idea how it works. You can then create your own scripts for the
>actual job.
>
>http://tennis.ecs.umass.edu/~czou/linux/backupSSH.html
>
>BR,
>
>Aarni
>
>
>On Monday 06 June 2005 20:07, you wrote:
>
>
>>hi all,
>>
>>i've googled and searche dmy pgsql books and i can't
>>quite get a confident grasp on cron jobs.
>>
>>as i understand it... the following should enable me
>>to backup via cron:
>>
>>PGUSER=postgres
>>PGPASSWORD=password
>>export PGUSER PGPASSWORD
>>pg_dump databasename | gzip > databasename.bak.gz
>>
>>my interfaces is cpanel and it only gives me one line
>>to type this in... can i replace the returns with a
>>space and will this still work?
>>
>>eg, PGUSER=postgres PGPASSWORD=password export...
>>
>>will databasename.bak.gz be stored in the same file as
>>my database?
>>
>>how can i store the file on my local computer?
>>
>>i finally have some data in my db and i scared of
>>losing it. *real* scared!
>>
>>tia...
>>
>>
>>
>>__________________________________
>>Discover Yahoo!
>>Have fun online with music videos, cool games, IM and more. Check it out!
>>http://discover.yahoo.com/online.html
>>
>>---------------------------(end of broadcast)---------------------------
>>TIP 5: Have you checked our extensive FAQ?
>>
>> http://www.postgresql.org/docs/faq
>>
>>
>
>
>

On Wednesday 08 June 2005 16:13, you wrote:
> Depends. If you have only one database, I suppose tar/zip everything
> together might be tempting. I host several domains, each with their own
> databases. I use pg_dump for data backup; use the original install package
> as backup to binaries; and make copies of config/startup.

Yes, depends.

I use sftp / ssh, cron jobs and little shell scripts to do various tasks. I
have also several virtual domains hosted with their own databases and amongst
other things take a nightly 'pg_dumpall -c'.

I also backup other system info and data like:

#!/bin/bash
#
#Backup-script
#
cd /var/backup # here we tar the files

tar cfz wusage.gz /var/wusage-accounts/

tar cfz pg_my.gz /usr/share/db_dumps/all/

tar cfz usr_local.gz /usr/local/

tar cfz etc.gz /etc/

...

And then retrieve stuff from /var/backup to other machines.

BR,

Aarni

>
> Frank
>
> At 10:11 AM 6/8/05, Todd Lewis wrote:
> >I've started looking at backup options myself. Which is better, tar
> >zipping the binaries and data files together, separately, or just running
> >pg_dump.
> >
> >Aarni Ruuhimäki wrote:
> >>Hi,
> >>
> >>Here's simple instructions and sample for sftp and cron (linux), which
> >>should give you an idea how it works. You can then create your own
> >>scripts for the actual job.
> >>
> >>http://tennis.ecs.umass.edu/~czou/linux/backupSSH.html
> >>
> >>BR,
> >>
> >>Aarni
> >>
> >>On Monday 06 June 2005 20:07, you wrote:
> >>>hi all,
> >>>
> >>>i've googled and searche dmy pgsql books and i can't
> >>>quite get a confident grasp on cron jobs.
> >>>
> >>>as i understand it... the following should enable me
> >>>to backup via cron:
> >>>
> >>>PGUSER=postgres
> >>>PGPASSWORD=password
> >>>export PGUSER PGPASSWORD
> >>>pg_dump databasename | gzip > databasename.bak.gz
> >>>
> >>>my interfaces is cpanel and it only gives me one line
> >>>to type this in... can i replace the returns with a
> >>>space and will this still work?
> >>>
> >>>eg, PGUSER=postgres PGPASSWORD=password export...
> >>>
> >>>will databasename.bak.gz be stored in the same file as
> >>>my database?
> >>>
> >>>how can i store the file on my local computer?
> >>>
> >>>i finally have some data in my db and i scared of
> >>>losing it. *real* scared!
> >>>
> >>>tia...
> >>>
> >>>
> >>>
> >>>__________________________________
> >>>Discover Yahoo!
> >>>Have fun online with music videos, cool games, IM and more. Check it
> >>> out! http://discover.yahoo.com/online.html
> >>>
> >>>---------------------------(end of broadcast)---------------------------
> >>>TIP 5: Have you checked our extensive FAQ?
> >>>
> >>> http://www.postgresql.org/docs/faq
> >
> >---------------------------(end of broadcast)---------------------------
> >TIP 5: Have you checked our extensive FAQ?
> >
> > http://www.postgresql.org/docs/faq
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo(at)postgresql(dot)org

--
Aarni Ruuhimäki
Megative Tmi
Pääsintie 26
45100 Kouvola
Finland
+358-5-3755035
+358-50-4910037

www.kymi.com | cfm.kymi.com
--------------
This is a bugfree broadcast to you
from **Kmail**
on **Fedora Core 2** linux system
--------------
Linux is like a wigwam - no windows, no gates and a free apache inside.