How to backup up the default Postgresql Database?

[ericevanz]

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.

[ericevanz]

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?

[ericevanz]

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.

[edgardcosta]

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

[sguenther]

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

[fifisteph]

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…

[bbukvic]

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

[ldepret]

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
‍‍‍

[kroser]

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?

[freelsd]

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!