|
|
# clean DB for creating an init DB
|
|
|
|
|
|
|
|
|
# clean temporary DB for creating an init DB
|
|
|
|
|
|
## Create temp database
|
|
|
First, create a temporary database (here called "betaforms") and duplicate production database (here called "framaforms") data into it
|
|
|
```
|
|
|
# export database without cache
|
|
|
pg_dump --clean --exclude-table-data="cache*" --if-exists framaforms > /tmp/tmp/framaforms_2018_04_02.sql
|
|
|
# prepare a clean temp database
|
|
|
# clean the temp database
|
|
|
dropdb betaforms
|
|
|
# create temp database (here "betaforms")
|
|
|
createdb --encoding=UTF8 --owner=betaforms betaforms
|
|
|
# import exported data into temp database
|
|
|
psql -U betaforms -W -h 127.0.0.1 betaforms < /tmp/tmp/framaforms_2018_04_02.sql
|
|
|
```
|
|
|
Then **very important step** before using drush on database: be sure that we will work on the desired database
|
|
|
```
|
|
|
cd /var/www/betaforms
|
|
|
drush status
|
|
|
pg_dump --clean --exclude-table-data="cache*" --if-exists framaforms > /tmp/tmp/framaforms_20180402.sql
|
|
|
psql -U betaforms -W -h 127.0.0.1 betaforms < /home/pyg/backups_pyg/framaforms_20180402.sql
|
|
|
```
|
|
|
|
|
|
# clean temporary DB for creating an init DB
|
|
|
|
|
|
## clean temp database
|
|
|
Connect to your temp database, and execute those queries
|
|
|
|
|
|
```sql
|
|
|
# enable module devel-generate to be able to use "drush genc"
|
|
|
drush pm-enable devel_generate -y
|
|
|
# clean all forms nodes (takes ~1mn for 1000 nodes). It's usefull cause it remove forms and reference to this form (but we could use some SQL queries instead)
|
|
|
drush genc --kill --types=form1 0 0
|
|
|
# disable devel-generate module
|
|
|
drush pm-disable devel_generate
|
|
|
|
|
|
#sql to empty DB
|
|
|
|
|
|
DELETE FROM node_revision
|
|
|
WHERE node_revision.nid in
|
|
|
(SELECT node_revision.nid FROM node_revision LEFT JOIN node ON (node_revision.nid = node.nid) WHERE node.type='form1' );
|
|
|
|
|
|
DELETE FROM pathauto_state
|
|
|
WHERE pathauto_state.entity_id in
|
|
|
(SELECT pathauto_state.entity_id FROM pathauto_state LEFT JOIN node ON (pathauto_state.entity_id = node.nid) WHERE node.type='form1' );
|
|
|
|
|
|
DELETE FROM url_alias
|
|
|
WHERE url_alias.alias IN
|
|
|
(SELECT url_alias.alias FROM node JOIN url_alias ON CONCAT('node/', node.nid) = url_alias.source WHERE node.type='form1')
|
|
|
|
|
|
DELETE FROM url_alias WHERE url_alias.source LIKE 'user/%'
|
|
|
|
|
|
-- delete all existing forms
|
|
|
DELETE FROM node WHERE type='form1';
|
|
|
|
|
|
-- sql to empty DB
|
|
|
TRUNCATE "batch", "cache", "cache_admin_menu", "cache_block", "cache_bootstrap", "cache_field", "cache_filter", "cache_form", "cache_image", "cache_libraries", "cache_menu", "cache_page", "cache_panels", "cache_path", "cache_rules", "cache_token", "cache_update", "cache_views", "cache_views_data", "flood", "history", "pgbar_webform_submissions_static", "search_dataset", "search_index", "search_total", "sessions", "themekey_ui_node_theme", "watchdog", "webform", "webform_component", "webform_conditional", "webform_conditional_actions", "webform_conditional_rules", "webform_confirm_email", "webform_confirm_email_code", "webform_confirm_email_confirmation_emails", "webform_confirmation", "webform_emails", "webform_last_download", "webform_report", "webform_roles", "webform_submissions", "webform_submitted_data", "webform_validation_rule", "webform_validation_rule_components";
|
|
|
|
|
|
#query to delete all users except admin
|
|
|
TRUNCATE "node_comment_statistics", "file_usage", "file_managed";
|
|
|
|
|
|
DELETE FROM field_data_body WHERE bundle='form1';
|
|
|
field_revision_body
|
|
|
|
|
|
|
|
|
field_data_field_form1_expiration
|
|
|
field_data_field_form1_file
|
|
|
field_data_field_form1_image
|
|
|
field_data_field_form1_progress
|
|
|
field_data_field_form1_results_access
|
|
|
field_data_field_form1_results_access
|
|
|
field_data_field_form1_template
|
|
|
field_data_field_form1_template_desc
|
|
|
field_revision_field_form1_image
|
|
|
field_revision_field_form1_expiration
|
|
|
field_revision_field_form1_file
|
|
|
field_revision_field_form1_progress
|
|
|
field_revision_field_form1_results_access
|
|
|
field_revision_field_form1_results_public
|
|
|
field_revision_field_form1_results_public
|
|
|
field_revision_field_form1_results_public
|
|
|
|
|
|
|
|
|
-- query to delete all users except admin
|
|
|
delete from users where uid != 0 and uid != 1;
|
|
|
|
|
|
#set "changemeASAP!" as the admin password
|
|
|
-- set "changemeASAP!" as the admin password
|
|
|
UPDATE "users" SET
|
|
|
"uid" = '1',
|
|
|
"name" = 'admin',
|
... | ... | @@ -52,6 +83,9 @@ UPDATE "users" SET |
|
|
"init" = 'you@yourdomain.net'
|
|
|
WHERE "name" = 'admin';
|
|
|
|
|
|
#export DB
|
|
|
-- export DB
|
|
|
pg_dump --clean --if-exists betaforms > /tmp/betaforms_20170728.sql
|
|
|
``` |
|
|
\ No newline at end of file |
|
|
```
|
|
|
|
|
|
## export cleaned database
|
|
|
pg_dump --clean --exclude-table-data="cache*" --if-exists betaforms > /tmp/tmp/framaforms_20161201.sql |
|
|
\ No newline at end of file |