1
0

make_full_schema.sh 5.4 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196
  1. #!/usr/bin/env bash
  2. #
  3. # This script generates SQL files for creating a brand new Synapse DB with the latest
  4. # schema, on both SQLite3 and Postgres.
  5. #
  6. # It does so by having Synapse generate an up-to-date SQLite DB, then running
  7. # synapse_port_db to convert it to Postgres. It then dumps the contents of both.
  8. export PGHOST="localhost"
  9. POSTGRES_DB_NAME="synapse_full_schema.$$"
  10. SQLITE_FULL_SCHEMA_OUTPUT_FILE="full.sql.sqlite"
  11. POSTGRES_FULL_SCHEMA_OUTPUT_FILE="full.sql.postgres"
  12. REQUIRED_DEPS=("matrix-synapse" "psycopg2")
  13. usage() {
  14. echo
  15. echo "Usage: $0 -p <postgres_username> -o <path> [-c] [-n] [-h]"
  16. echo
  17. echo "-p <postgres_username>"
  18. echo " Username to connect to local postgres instance. The password will be requested"
  19. echo " during script execution."
  20. echo "-c"
  21. echo " CI mode. Enables coverage tracking and prints every command that the script runs."
  22. echo "-o <path>"
  23. echo " Directory to output full schema files to."
  24. echo "-h"
  25. echo " Display this help text."
  26. }
  27. while getopts "p:co:h" opt; do
  28. case $opt in
  29. p)
  30. export PGUSER=$OPTARG
  31. ;;
  32. c)
  33. # Print all commands that are being executed
  34. set -x
  35. # Modify required dependencies for coverage
  36. REQUIRED_DEPS+=("coverage" "coverage-enable-subprocess")
  37. COVERAGE=1
  38. ;;
  39. o)
  40. command -v realpath > /dev/null || (echo "The -o flag requires the 'realpath' binary to be installed" && exit 1)
  41. OUTPUT_DIR="$(realpath "$OPTARG")"
  42. ;;
  43. h)
  44. usage
  45. exit
  46. ;;
  47. \?)
  48. echo "ERROR: Invalid option: -$OPTARG" >&2
  49. usage
  50. exit
  51. ;;
  52. esac
  53. done
  54. # Check that required dependencies are installed
  55. unsatisfied_requirements=()
  56. for dep in "${REQUIRED_DEPS[@]}"; do
  57. pip show "$dep" --quiet || unsatisfied_requirements+=("$dep")
  58. done
  59. if [ ${#unsatisfied_requirements} -ne 0 ]; then
  60. echo "Please install the following python packages: ${unsatisfied_requirements[*]}"
  61. exit 1
  62. fi
  63. if [ -z "$PGUSER" ]; then
  64. echo "No postgres username supplied"
  65. usage
  66. exit 1
  67. fi
  68. if [ -z "$OUTPUT_DIR" ]; then
  69. echo "No output directory supplied"
  70. usage
  71. exit 1
  72. fi
  73. # Create the output directory if it doesn't exist
  74. mkdir -p "$OUTPUT_DIR"
  75. read -rsp "Postgres password for '$PGUSER': " PGPASSWORD
  76. echo ""
  77. export PGPASSWORD
  78. # Exit immediately if a command fails
  79. set -e
  80. # cd to root of the synapse directory
  81. cd "$(dirname "$0")/.."
  82. # Create temporary SQLite and Postgres homeserver db configs and key file
  83. TMPDIR=$(mktemp -d)
  84. KEY_FILE=$TMPDIR/test.signing.key # default Synapse signing key path
  85. SQLITE_CONFIG=$TMPDIR/sqlite.conf
  86. SQLITE_DB=$TMPDIR/homeserver.db
  87. POSTGRES_CONFIG=$TMPDIR/postgres.conf
  88. # Ensure these files are delete on script exit
  89. trap 'rm -rf $TMPDIR' EXIT
  90. cat > "$SQLITE_CONFIG" <<EOF
  91. server_name: "test"
  92. signing_key_path: "$KEY_FILE"
  93. macaroon_secret_key: "abcde"
  94. report_stats: false
  95. database:
  96. name: "sqlite3"
  97. args:
  98. database: "$SQLITE_DB"
  99. # Suppress the key server warning.
  100. trusted_key_servers: []
  101. EOF
  102. cat > "$POSTGRES_CONFIG" <<EOF
  103. server_name: "test"
  104. signing_key_path: "$KEY_FILE"
  105. macaroon_secret_key: "abcde"
  106. report_stats: false
  107. database:
  108. name: "psycopg2"
  109. args:
  110. user: "$PGUSER"
  111. host: "$PGHOST"
  112. password: "$PGPASSWORD"
  113. database: "$POSTGRES_DB_NAME"
  114. # Suppress the key server warning.
  115. trusted_key_servers: []
  116. EOF
  117. # Generate the server's signing key.
  118. echo "Generating SQLite3 db schema..."
  119. python -m synapse.app.homeserver --generate-keys -c "$SQLITE_CONFIG"
  120. # Make sure the SQLite3 database is using the latest schema and has no pending background update.
  121. echo "Running db background jobs..."
  122. synapse/_scripts/update_synapse_database.py --database-config --run-background-updates "$SQLITE_CONFIG"
  123. # Create the PostgreSQL database.
  124. echo "Creating postgres database..."
  125. createdb --lc-collate=C --lc-ctype=C --template=template0 "$POSTGRES_DB_NAME"
  126. echo "Copying data from SQLite3 to Postgres with synapse_port_db..."
  127. if [ -z "$COVERAGE" ]; then
  128. # No coverage needed
  129. synapse/_scripts/synapse_port_db.py --sqlite-database "$SQLITE_DB" --postgres-config "$POSTGRES_CONFIG"
  130. else
  131. # Coverage desired
  132. coverage run synapse/_scripts/synapse_port_db.py --sqlite-database "$SQLITE_DB" --postgres-config "$POSTGRES_CONFIG"
  133. fi
  134. # Delete schema_version, applied_schema_deltas and applied_module_schemas tables
  135. # Also delete any shadow tables from fts4
  136. # This needs to be done after synapse_port_db is run
  137. echo "Dropping unwanted db tables..."
  138. SQL="
  139. DROP TABLE schema_version;
  140. DROP TABLE applied_schema_deltas;
  141. DROP TABLE applied_module_schemas;
  142. DROP TABLE event_search_content;
  143. DROP TABLE event_search_segments;
  144. DROP TABLE event_search_segdir;
  145. DROP TABLE event_search_docsize;
  146. DROP TABLE event_search_stat;
  147. DROP TABLE user_directory_search_content;
  148. DROP TABLE user_directory_search_segments;
  149. DROP TABLE user_directory_search_segdir;
  150. DROP TABLE user_directory_search_docsize;
  151. DROP TABLE user_directory_search_stat;
  152. "
  153. sqlite3 "$SQLITE_DB" <<< "$SQL"
  154. psql "$POSTGRES_DB_NAME" -w <<< "$SQL"
  155. echo "Dumping SQLite3 schema to '$OUTPUT_DIR/$SQLITE_FULL_SCHEMA_OUTPUT_FILE'..."
  156. sqlite3 "$SQLITE_DB" ".dump" > "$OUTPUT_DIR/$SQLITE_FULL_SCHEMA_OUTPUT_FILE"
  157. echo "Dumping Postgres schema to '$OUTPUT_DIR/$POSTGRES_FULL_SCHEMA_OUTPUT_FILE'..."
  158. pg_dump --format=plain --no-tablespaces --no-acl --no-owner $POSTGRES_DB_NAME | sed -e '/^--/d' -e 's/public\.//g' -e '/^SET /d' -e '/^SELECT /d' > "$OUTPUT_DIR/$POSTGRES_FULL_SCHEMA_OUTPUT_FILE"
  159. echo "Cleaning up temporary Postgres database..."
  160. dropdb $POSTGRES_DB_NAME
  161. echo "Done! Files dumped to: $OUTPUT_DIR"