database.md (7392B)
1 # Database 2 3 GoToSocial stores statuses, accounts, etc, in a database. This can be either [SQLite](https://sqlite.org/index.html) or [Postgres](https://www.postgresql.org/). 4 5 By default, GoToSocial will use Postgres, but this is easy to change. 6 7 ## SQLite 8 9 SQLite, as the name implies, is the lightest database type that GoToSocial can use. It stores entries in a simple file format, usually in the same directory as the GoToSocial binary itself. SQLite is great for small instances and lower-powered machines like Raspberry Pi, where a dedicated database would be overkill. 10 11 To configure GoToSocial to use SQLite, change `db-type` to `sqlite`. The `address` setting will then be a filename instead of an address, so you will want to change it to `sqlite.db` or something similar. 12 13 Note that the `:memory:` setting will use an *in-memory database* which will be wiped when your GoToSocial instance stops running. This is for testing only and is absolutely not suitable for running a proper instance, so *don't do this*. 14 15 ## Postgres 16 17 Postgres is a heavier database format, which is useful for larger instances where you need to scale performance, or where you need to run your database on a dedicated machine separate from your GoToSocial instance (or do funky stuff like run a database cluster). 18 19 You can connect to Postgres using either a Unix socket connection, or via TCP, depending on what you've set as your `db-address` value. 20 21 GoToSocial also supports connecting to Postgres using SSL/TLS over TCP. If you're running Postgres on a different machine from GoToSocial, and connecting to it via an IP address or hostname (as opposed to just running on localhost), then SSL/TLS is **CRUCIAL** to avoid leaking data all over the place! 22 23 When you're using Postgres, GoToSocial expects whatever you've set for `db-user` to already be created in the database, and to have ownership of whatever you've set for `db-database`. 24 25 For example, if you set: 26 27 ```text 28 db: 29 [...] 30 user: "gotosocial" 31 password: "some_really_good_password" 32 database: "gotosocial" 33 ``` 34 35 Then you should have already created database `gotosocial` in Postgres, and given ownership of it to the `gotosocial` user. 36 37 The psql commands to do this will look something like: 38 39 ```psql 40 create database gotosocial with locale C.UTF-8 template template0; 41 create user gotosocial with password 'some_really_good_password'; 42 grant all privileges on database gotosocial to gotosocial; 43 ``` 44 45 GoToSocial makes use of ULIDs (Universally Unique Lexicographically Sortable Identifiers) which will not work in non-English collate environments. For this reason it is important to create the database with `C.UTF-8` locale. To do that on systems which were already initialized with non-C locale, `template0` pristine database template must be used. 46 47 ## Settings 48 49 ```yaml 50 ############################ 51 ##### DATABASE CONFIG ###### 52 ############################ 53 54 # Config pertaining to the Gotosocial database connection 55 56 # String. Database type. 57 # Options: ["postgres","sqlite"] 58 # Default: "postgres" 59 db-type: "postgres" 60 61 # String. Database address or parameters. 62 # 63 # For Postgres, this should be the address or socket at which the database can be reached. 64 # 65 # For Sqlite, this should be the path to your sqlite database file. Eg., /opt/gotosocial/sqlite.db. 66 # If the file doesn't exist at the specified path, it will be created. 67 # If just a filename is provided (no directory) then the database will be created in the same directory 68 # as the GoToSocial binary. 69 # If address is set to :memory: then an in-memory database will be used (no file). 70 # WARNING: :memory: should NOT BE USED except for testing purposes. 71 # 72 # Examples: ["localhost","my.db.host","127.0.0.1","192.111.39.110",":memory:", "sqlite.db"] 73 # Default: "" 74 db-address: "" 75 76 # Int. Port for database connection. 77 # Examples: [5432, 1234, 6969] 78 # Default: 5432 79 db-port: 5432 80 81 # String. Username for the database connection. 82 # Examples: ["mydbuser","postgres","gotosocial"] 83 # Default: "" 84 db-user: "" 85 86 # String. Password to use for the database connection 87 # Examples: ["password123","verysafepassword","postgres"] 88 # Default: "" 89 db-password: "" 90 91 # String. Name of the database to use within the provided database type. 92 # Examples: ["mydb","postgres","gotosocial"] 93 # Default: "gotosocial" 94 db-database: "gotosocial" 95 96 # String. Disable, enable, or require SSL/TLS connection to the database. 97 # If "disable" then no TLS connection will be attempted. 98 # If "enable" then TLS will be tried, but the database certificate won't be checked (for self-signed certs). 99 # If "require" then TLS will be required to make a connection, and a valid certificate must be presented. 100 # Options: ["disable", "enable", "require"] 101 # Default: "disable" 102 db-tls-mode: "disable" 103 104 # String. Path to a CA certificate on the host machine for db certificate validation. 105 # If this is left empty, just the host certificates will be used. 106 # If filled in, the certificate will be loaded and added to host certificates. 107 # Examples: ["/path/to/some/cert.crt"] 108 # Default: "" 109 db-tls-ca-cert: "" 110 111 # Int. Number to multiply by CPU count to set permitted total of open database connections (in-use and idle). 112 # You can use this setting to tune your database connection behavior, though most admins won't need to touch it. 113 # 114 # Example values for multiplier 8: 115 # 116 # 1 cpu = 08 open connections 117 # 2 cpu = 16 open connections 118 # 4 cpu = 32 open connections 119 # 120 # Example values for multiplier 4: 121 # 122 # 1 cpu = 04 open connections 123 # 2 cpu = 08 open connections 124 # 4 cpu = 16 open connections 125 # 126 # A multiplier of 8 is a sensible default, but you may wish to increase this for instances 127 # running on very performant hardware, or decrease it for instances using v. slow CPUs. 128 # 129 # If you set the multiplier to less than 1, only one open connection will be used regardless of cpu count. 130 # 131 # PLEASE NOTE!!: This setting currently only applies for Postgres. SQLite will always use 1 connection regardless 132 # of what is set here. This behavior will change in future when we implement better SQLITE_BUSY handling. 133 # See https://github.com/superseriousbusiness/gotosocial/issues/1407 for more details. 134 # 135 # Examples: [16, 8, 10, 2] 136 # Default: 8 137 db-max-open-conns-multiplier: 8 138 139 # String. SQLite journaling mode. 140 # SQLite only -- unused otherwise. 141 # If set to empty string, the sqlite default will be used. 142 # See: https://www.sqlite.org/pragma.html#pragma_journal_mode 143 # Examples: ["DELETE", "TRUNCATE", "PERSIST", "MEMORY", "WAL", "OFF"] 144 # Default: "WAL" 145 db-sqlite-journal-mode: "WAL" 146 147 # String. SQLite synchronous mode. 148 # SQLite only -- unused otherwise. 149 # If set to empty string, the sqlite default will be used. 150 # See: https://www.sqlite.org/pragma.html#pragma_synchronous 151 # Examples: ["OFF", "NORMAL", "FULL", "EXTRA"] 152 # Default: "NORMAL" 153 db-sqlite-synchronous: "NORMAL" 154 155 # Byte size. SQlite cache size. 156 # SQLite only -- unused otherwise. 157 # If set to empty string or zero, the sqlite default (2MiB) will be used. 158 # See: https://www.sqlite.org/pragma.html#pragma_cache_size 159 # Examples: ["0", "2MiB", "8MiB", "64MiB"] 160 # Default: "8MiB" 161 db-sqlite-cache-size: "8MiB" 162 163 # Duration. SQlite busy timeout. 164 # SQLite only -- unused otherwise. 165 # If set to empty string or zero, the sqlite default will be used. 166 # See: https://www.sqlite.org/pragma.html#pragma_busy_timeout 167 # Examples: ["0s", "1s", "30s", "1m", "5m"] 168 # Default: "5s" 169 db-sqlite-busy-timeout: "5m" 170 ```