How to rebuild database / recreate alfresco database?

[jblank-a]

I'm attempting to rebuild the entire 'alfresco' database (on Alfresco Community Edition 4.1) without re-running the installation script. Accordingly, I've dropped all tables within the 'alfresco' database.

I restart Tomcat (and thus Alfresco), but the databases are not re-created.

Instead, I get output like:

' 2014-08-15 03:28:42,430  WARN  [domain.schema.SchemaBootstrap] [localhost-startStop-1] Schema validation found 81 potential problems, results written to: /opt/apache-tomcat/temp/Alfresco/Alfresco-PostgreSQLDialect-Validation-Pre-Upgrade-alf_-8001599409053020254.txt
2014-08-15 03:28:42,451  WARN  [domain.schema.SchemaBootstrap] [localhost-startStop-1] Schema validation found 14 potential problems, results written to: /opt/apache-tomcat/temp/Alfresco/Alfresco-PostgreSQLDialect-Validation-Pre-Upgrade-avm_-1645115885481692282.txt
2014-08-15 03:28:42,484  WARN  [domain.schema.SchemaBootstrap] [localhost-startStop-1] Schema validation found 28 potential problems, results written to: /opt/apache-tomcat/temp/Alfresco/Alfresco-PostgreSQLDialect-Validation-Pre-Upgrade-jbpm_-6786442235680810809.txt'

…and the text files thus referenced contain lines like:

'Difference: missing sequence from database, expected at path: .avm_nodes_seq
Difference: missing sequence from database, expected at path: .avm_store_properties_seq
Difference: missing sequence from database, expected at path: .avm_stores_seq
Difference: missing sequence from database, expected at path: .avm_version_roots_seq
Difference: missing table from database, expected at path: .avm_aspects
Difference: missing table from database, expected at path: .avm_child_entries
Difference: missing table from database, expected at path: .avm_history_links
Difference: missing table from database, expected at path: .avm_merge_links
Difference: missing table from database, expected at path: .avm_node_properties'

Alfresco starts up— sort of— but I can't log in, and the database tables are not being recreated:

'alfresco=# \dt
No relations found.
alfresco=# '

I've googled and googled and googled, but all the information I can find has not been of help.

Please help. This is driving me batty! How can I get the database recreated?

[bostaxola]

You should crate the databases manually, stop alfresco and tomcat, log in into postgresql and run the following:

CREATE DATABASE alfresco WITH OWNER alfresco;
GRANT ALL PRIVILEGES ON DATABASE alfresco TO alfresco;
‍‍‍

[jblank-a]

I already did that. Upon startup, Alfresco isn't recreating any of the tables.

[bostaxola]

I've not found it for your Alfresco version, but you could try the following sql scripts from the 4.0e: http://svn.alfresco.com/repos/alfresco-open-mirror/alfresco/COMMUNITYTAGS/V4.0e/root/projects/reposi...
Once done it, you can upgrade it to the 4.1 comparing 4.0's and 4.1's upgrade dbscript's from the 4.2 http://svn.alfresco.com/repos/alfresco-open-mirror/alfresco/COMMUNITYTAGS/V4.2a/root/projects/reposi...

There should be another way, a script that creates again the tables, but since I don't know… Hope you find it helpful.

[cver]

Hi, since I didn't find any confirmation on the Net, I ask here : do you mean that using only the filesystem (contenstore), the whole database (and not only teh index) can be re-built automatically ? I noticed already that Alfresco writes a huge number of very small files every second, but I couldn't find assurance that a backup of only the filesystem would allow to recover the documents. On the contrary, I found several pages talking about orphans, liabilities between DB and filesystems etc, and that is a bit scary.

Note that I'm using Entreprise 4.0.2.9, not community. But recovery to community would also fit my needs (it's only for severe DR situations)

[mrogers]

If there's no content store.   Then the database is created.

But to the OP.  Why are you doing this?  If its just a clear out to restore default settings then as well as a drop and recreate of the empty db you also need to delete/remove the content store and index.

Cver, what to backup depends upon your requirements.   With PostgreSQL if alfresco is not running then a file system backup should be  o.k.   If you are using another database or hot backup then you will need the content store and database dump.   As per the backup docs.