topic Re: How to backup up the default Postgresql Database? in Alfresco Archive

How to backup up the default Postgresql Database?

ericevanz — Wed, 12 Oct 2011 15:32:01 GMT

Hi,I used the bundled Linux installer to install Community 4.0a. How do I backup the included Postgresql Database? My attempts to at using the psql and postgres commands to login into SQL prompt me to install the PostgreSql package, but I know it's already running.

Re: How to backup up the default Postgresql Database?

ericevanz — Wed, 12 Oct 2011 18:42:20 GMT

figured that out… had to "./" my psql command, now I have new problem. Cannot login into postgres when Alfresco is stopped. Should one backup the database while it's live then? Then stop Alfresco and backup alf_data?

Re: How to backup up the default Postgresql Database?

ericevanz — Wed, 12 Oct 2011 19:03:01 GMT

I think I just needed to eat something: I figured that out too… pg_dump can be done live according to postgres documentation, so that's that.

Maybe this will help other newbs doing this for their co.'s that want DM but don't want to pay for it.

Re: How to backup up the default Postgresql Database?

edgardcosta — Thu, 05 Jan 2012 19:21:16 GMT

HI

I think that we have to is edit the pg_hba_conf.sample that is in ../postgresql/share with the common postgresql configuration. I, yet, dont try. But I think that is the way to get a correct backup

EdgardCosta

Re: How to backup up the default Postgresql Database?

sguenther — Sat, 07 Jan 2012 14:08:02 GMT

Hi,

the postgresql package of alfresco already includes the two tools pg_dump and pg_dump all.

And your pg_hba.conf file already contains a line like this:

host all all 127.0.0.1/32 md5

You should therefore be able to backup either the alfresco database or the whole server with the following two command:

PATH_TO_PG_DUMP/pg_dump -h 127.0.0.1 -p 5432 -U alfresco -f pg_dump.sql alfresco
PATH_TO_PG_DUMP_ALL/pg_dumpall -c -h 127.0.0.1 -p 5432 -U postgres -f pg_dumpall.sql

If you do not want to enter the password, create a file called .pgpass in your home directory, contaning this line

localhost:5432:*ostgresECRET

Stefan

Re: How to backup up the default Postgresql Database?

fifisteph — Wed, 11 Jan 2012 17:59:51 GMT

Very interesting but where do you get the postgres password after a fresh install of alfresco ? (I mean on Windows )

Edit: Ok forget it I've found it…

Re: How to backup up the default Postgresql Database?

bbukvic — Mon, 30 Jul 2012 06:46:45 GMT

Very interesting but where do you get the postgres password after a fresh install of alfresco ? (I mean on Windows )

Edit: Ok forget it I've found it…

Hello, can you help me and tell me where you find postgres password?
Tnx in advance

Re: How to backup up the default Postgresql Database?

ldepret — Fri, 26 Oct 2012 14:10:36 GMT

The postgres password is the same as the Alfresco admin password you choose during the install, for example 'mysecret'.

The password can also be found in alfresco-global.properties:

db.driver=org.postgresql.Driver
db.username=alfresco
db.password=mysecret
db.name=alfresco
db.url=jdbc:postgresql://localhost:5432/${db.name}
‍‍‍‍‍‍

You also have to supply the 'postgres' username to the psql command.
So to restore a postgres dump you have to do something like:

psql -U postgres < pg_dumpall.sql
Password for user postgres:   mysecret
‍‍‍

To access the database with the alfresco user:

psql -U alfresco
Password for user alfresco:   mysecret
‍‍‍

Re: How to backup up the default Postgresql Database?

kroser — Fri, 18 Jan 2013 16:29:37 GMT

In previous posts, the question of how to retrieve the "alfresco" user password for the database was answered. In my case I'm trying to use the pg_dumpall command and I'm given a password prompt which is for what I assume to be the "postgres" user. How do I determine the postgres password?

Re: How to backup up the default Postgresql Database?

freelsd — Tue, 22 Jan 2013 17:09:49 GMT

Hi all! Would you help me please!

There are a lot of blogs/articles about how to backup & restore Alfresco DB, but there is everywere spoken in context of Linux/BSD operating systems! But how about MS Windows?

Here we are:
OS Windows Server 2003 R2 32bit
Alfresco 4.0
with Postage SQL and Tomcat web server
pgAdmin III 1.6 ODBC
So the situation is that we can not change operating system, and have to make scheduled backups in this, current configuration. Current DB has a lot of useful content and should be backed up properly as is!

I'm sick'n'tired of searcing and can not find clear "Alfresco Database Backup&Restore step-by-step HOWTO for Windows" !!!
Maybe someone would try to provide list of steps for backing up & restore it with a few comments?!

What i have is only:
1) manually created backup of database "alfresco" daily.backup created with pgAdmin III tool: in a tree Servers - Alfresco - Databases - alfresco - Right click - Backup…: type "tar" and included sections Pre-data, Data, Post-data
2) and alf_data folder copied apart!

Thank you in advance!

Re: How to backup up the default Postgresql Database?

isra_vass — Wed, 23 Jan 2013 16:36:30 GMT

Well, i think that all you need to do is configure an scheduled backup of the database.

For windows, you can follow this:

http://wiki.postgresql.org/wiki/Automated_Backup_on_Windows

The fact, is that in windows you will need to have any tool to configure a scheduled task to do that, but should´t be difficult.

I hope this helps,

Regards

Re: How to backup up the default Postgresql Database?

johnpelquingua — Tue, 26 Feb 2013 06:10:07 GMT

Hi Isra,

Thank you for the tips to have a scheduled backup of the database. Really helps!

Thanks~
JP

Re: How to backup up the default Postgresql Database?

cibex — Tue, 26 Mar 2013 14:35:22 GMT

#1 stopping alfresco services
./opt/alfresco/alfresco.sh stop

#2 backup file system
tar czvf /opt/alfresco-bkp/alf_data_$(date +%Y%m%d-%H%M%S).tgz /opt/alfresco/alf_data

#3 backup alfresco database (postgres needs to be started)
# don´t mix up os user and postgres user, by default there is no alfresco os user

/opt/alfresco/postgresql/bin
su postgres ./pg_ctl start
su postgres -c './pg_dump -U alfresco alfresco | gzip > /opt/alfresco-bkp/postgres_$(date +%Y%m%d-%H%M%S).dmp.gz'
su postgres ./pg_ctl stop

#4 starting alfresco services
./opt/alfresco/alfresco.sh start

I found this article helpful to create a cron job: http://grover.open2space.com/content/postgresql-84-database-backups-pg_dump-and-cron

kr

Josef

Re: How to backup up the default Postgresql Database?

gopei — Wed, 04 Sep 2013 02:53:35 GMT

Hi cibex,.. thanks for your command to create backup. but, this is step for backup.

how to step for restore after create backup step? thanks

Re: How to backup up the default Postgresql Database?

gopei — Wed, 04 Sep 2013 06:11:30 GMT

after I used the bundled Linux installer to install Community 4.2.c ib ubuntu 12.04 OS server, to install Community on different server machines

the first one I did was:

1> compress all of the directory. (/opt/alfresco-4.2.c)

2> backup database with like this:
/opt/alfresco-4.2.c/postgresql/bin/pg_dump-U alfresco alfresco | gzip > /opt / postgres_ $ (date +% Y% m% d-% H% M% S). dmp.gz

then

1> rename old directory like this (/opt/alfresco-4.2.c with /opt /alfresco_old)

2> extract it to a new server machine (/opt/alfresco-4.2.c)

3> and restore postgre database like this
/opt/alfresco-4.2.c/postgresql/bin/psql-U alfresco alfresco </opt/postgres_20130904-093544.dmp.

(note: all the processes that I did with alfresco conditions of service is not running).

However, it still does not run postgre database. sample in console :

root@dms1:~# /opt/alfresco-4.2.c/postgresql/scripts/ctl.sh status
postgresql not running

root@dms1:~# /opt/alfresco-4.2.c/postgresql/scripts/ctl.sh start
could not change directory to "/root"
waiting for server to start………………………………………………………pg_ctl.bin:
could not start server
Examine the log output.
/opt/alfresco-4.2.c/postgresql/scripts/ctl.sh : postgresql could not be started

where lies the fault?
Can you help me? is there a step by step for an easier way to backup and restore of alfresco?

many thanks and best regards.
gopei