Re: Shell script to Backup/Dump Database

Thanks for the reply.

I will test today.

There are some good practices that I should do? For example, I have this
database in a shared hosting, should I add the functionality of send the
dumps by FTP to my PC once a week? Give some more ideias to avoid data loss
in case of disaster.

Best Regards,

On Wed, May 12, 2010 at 4:45 PM, Thom Brown <thom(at)linux(dot)com> wrote:

> On 12 May 2010 16:30, Andre Lopes <lopes80andre(at)gmail(dot)com> wrote:
>
>> Hi,
>>
>> I'am looking fo a shell script do Backup/Dump my PostgreSQL twice a day.
>>
>> I will need to do a full dump (schema + data), schema dump (only schema)
>> and data (only data)
>>
>> There is something done on this subject? I'am very poor in shell script,
>> there are some scripts on the web ready to use?
>>
>>
>> Best Regards,
>>
>
> Yes, several of us discussed something like this recently. Are you looking
> to back up all databases on the cluster or just an individual database?
>
> If it's a cluster, you could have a shell script like this:
>
> #!/bin/bash
>
> mkdir /tmp/`date +\%Y\%m\%d`
> pg_dumpall -U postgres | gzip > /tmp/`date +\%Y\%m\%d`/FULL.sql.gz
> pg_dumpall -c -U postgres | gzip > /tmp/`date +\%Y\%m\%d`/SCHEMA.sql.gz
> pg_dumpall -a -U postgres | gzip > /tmp/`date +\%Y\%m\%d`/DATA.sql.gz
>
>
> If you want a single database, try this:
>
> #!/bin/bash
>
> mkdir /tmp/`date +\%Y\%m\%d`
> pg_dump -U postgres my_database | gzip > /tmp/`date
> +\%Y\%m\%d`/my_database_FULL.sql.gz
> pg_dump -s -U postgres my_database | gzip > /tmp/`date
> +\%Y\%m\%d`/my_database_SCHEMA.sql.gz
> pg_dump -a -U postgres my_database | gzip > /tmp/`date
> +\%Y\%m\%d`/my_database_DATA.sql.gz
>
>
> If you want each individual database, try this:
>
> #!/bin/bash
>
> mkdir /tmp/`date +\%Y\%m\%d`
> query="select datname from pg_database where not datistemplate and
> datallowconn;"
> for line in `psql -U postgres -At -c "$query" postgres`
> do
> pg_dump -U postgres "$line" | gzip > /tmp/`date
> +\%Y\%m\%d`/"$line"_FULL.sql.gz
> pg_dump -s -U postgres "$line" | gzip > /tmp/`date
> +\%Y\%m\%d`/"$line"_SCHEMA.sql.gz
> pg_dump -a -U postgres "$line" | gzip > /tmp/`date
> +\%Y\%m\%d`/"$line"_DATA.sql.gz
> done
>
> Obviously you can change the output location to something other that /tmp
> if you're not going to transfer it anywhere. Just make sure the path
> exists. If you are copying it away immediately after, make sure you delete
> it or they'll build up.
>
> If you want to schedule it, bung it in a shell script like
> backup_databases.sh and stick it in the root crontab.
>
> Regards
>
> Thom
>