make_full_schema.sh 5.4 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195
  1. #!/bin/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. POSTGRES_HOST="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. POSTGRES_USERNAME=$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 "$POSTGRES_USERNAME" ]; 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 '$POSTGRES_USERNAME': " POSTGRES_PASSWORD
  76. echo ""
  77. # Exit immediately if a command fails
  78. set -e
  79. # cd to root of the synapse directory
  80. cd "$(dirname "$0")/.."
  81. # Create temporary SQLite and Postgres homeserver db configs and key file
  82. TMPDIR=$(mktemp -d)
  83. KEY_FILE=$TMPDIR/test.signing.key # default Synapse signing key path
  84. SQLITE_CONFIG=$TMPDIR/sqlite.conf
  85. SQLITE_DB=$TMPDIR/homeserver.db
  86. POSTGRES_CONFIG=$TMPDIR/postgres.conf
  87. # Ensure these files are delete on script exit
  88. trap 'rm -rf $TMPDIR' EXIT
  89. cat > "$SQLITE_CONFIG" <<EOF
  90. server_name: "test"
  91. signing_key_path: "$KEY_FILE"
  92. macaroon_secret_key: "abcde"
  93. report_stats: false
  94. database:
  95. name: "sqlite3"
  96. args:
  97. database: "$SQLITE_DB"
  98. # Suppress the key server warning.
  99. trusted_key_servers: []
  100. EOF
  101. cat > "$POSTGRES_CONFIG" <<EOF
  102. server_name: "test"
  103. signing_key_path: "$KEY_FILE"
  104. macaroon_secret_key: "abcde"
  105. report_stats: false
  106. database:
  107. name: "psycopg2"
  108. args:
  109. user: "$POSTGRES_USERNAME"
  110. host: "$POSTGRES_HOST"
  111. password: "$POSTGRES_PASSWORD"
  112. database: "$POSTGRES_DB_NAME"
  113. # Suppress the key server warning.
  114. trusted_key_servers: []
  115. EOF
  116. # Generate the server's signing key.
  117. echo "Generating SQLite3 db schema..."
  118. python -m synapse.app.homeserver --generate-keys -c "$SQLITE_CONFIG"
  119. # Make sure the SQLite3 database is using the latest schema and has no pending background update.
  120. echo "Running db background jobs..."
  121. scripts-dev/update_database --database-config "$SQLITE_CONFIG"
  122. # Create the PostgreSQL database.
  123. echo "Creating postgres database..."
  124. createdb $POSTGRES_DB_NAME
  125. echo "Copying data from SQLite3 to Postgres with synapse_port_db..."
  126. if [ -z "$COVERAGE" ]; then
  127. # No coverage needed
  128. scripts/synapse_port_db --sqlite-database "$SQLITE_DB" --postgres-config "$POSTGRES_CONFIG"
  129. else
  130. # Coverage desired
  131. coverage run scripts/synapse_port_db --sqlite-database "$SQLITE_DB" --postgres-config "$POSTGRES_CONFIG"
  132. fi
  133. # Delete schema_version, applied_schema_deltas and applied_module_schemas tables
  134. # Also delete any shadow tables from fts4
  135. # This needs to be done after synapse_port_db is run
  136. echo "Dropping unwanted db tables..."
  137. SQL="
  138. DROP TABLE schema_version;
  139. DROP TABLE applied_schema_deltas;
  140. DROP TABLE applied_module_schemas;
  141. DROP TABLE event_search_content;
  142. DROP TABLE event_search_segments;
  143. DROP TABLE event_search_segdir;
  144. DROP TABLE event_search_docsize;
  145. DROP TABLE event_search_stat;
  146. DROP TABLE user_directory_search_content;
  147. DROP TABLE user_directory_search_segments;
  148. DROP TABLE user_directory_search_segdir;
  149. DROP TABLE user_directory_search_docsize;
  150. DROP TABLE user_directory_search_stat;
  151. "
  152. sqlite3 "$SQLITE_DB" <<< "$SQL"
  153. psql $POSTGRES_DB_NAME -U "$POSTGRES_USERNAME" -w <<< "$SQL"
  154. echo "Dumping SQLite3 schema to '$OUTPUT_DIR/$SQLITE_FULL_SCHEMA_OUTPUT_FILE'..."
  155. sqlite3 "$SQLITE_DB" ".dump" > "$OUTPUT_DIR/$SQLITE_FULL_SCHEMA_OUTPUT_FILE"
  156. echo "Dumping Postgres schema to '$OUTPUT_DIR/$POSTGRES_FULL_SCHEMA_OUTPUT_FILE'..."
  157. 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"
  158. echo "Cleaning up temporary Postgres database..."
  159. dropdb $POSTGRES_DB_NAME
  160. echo "Done! Files dumped to: $OUTPUT_DIR"