git.squeep.com Git - akkoma/blob - docs/docs/administration/CLI_tasks/database.md

   1 # Database maintenance tasks
   2
   3 {! administration/CLI_tasks/general_cli_task_info.include !}
   4
   5 !!! danger
   6     These mix tasks can take a long time to complete. Many of them were written to address specific database issues that happened because of bugs in migrations or other specific scenarios. Do not run these tasks "just in case" if everything is fine your instance.
   7
   8 ## Replace embedded objects with their references
   9
  10 Replaces embedded objects with references to them in the `objects` table. Only needs to be ran once if the instance was created before Pleroma 1.0.5. The reason why this is not a migration is because it could significantly increase the database size after being ran, however after this `VACUUM FULL` will be able to reclaim about 20% (really depends on what is in the database, your mileage may vary) of the db size before the migration.
  11
  12 === "OTP"
  13
  14     ```sh
  15     ./bin/pleroma_ctl database remove_embedded_objects [option ...]
  16     ```
  17
  18 === "From Source"
  19
  20     ```sh
  21     mix pleroma.database remove_embedded_objects [option ...]
  22     ```
  23
  24 ### Options
  25 - `--vacuum` - run `VACUUM FULL` after the embedded objects are replaced with their references
  26
  27 ## Prune old remote posts from the database
  28
  29 This will prune remote posts older than 90 days (configurable with [`config :pleroma, :instance, remote_post_retention_days`](../../configuration/cheatsheet.md#instance)) from the database. Pruned posts may be refetched in some cases.
  30
  31 !!! note
  32     The disk space will only be reclaimed after a proper vacuum. By default Postgresql does this for you on a regular basis, but if your instance has been running for a long time and there are many rows deleted, it may be advantageous to use `VACUUM FULL` (e.g. by using the `--vacuum` option).
  33
  34 !!! danger
  35     You may run out of disk space during the execution of the task or vacuuming if you don't have about 1/3rds of the database size free. Vacuum causes a substantial increase in I/O traffic, and may lead to a degraded experience while it is running.
  36
  37 === "OTP"
  38
  39     ```sh
  40     ./bin/pleroma_ctl database prune_objects [option ...]
  41     ```
  42
  43 === "From Source"
  44
  45     ```sh
  46     mix pleroma.database prune_objects [option ...]
  47     ```
  48
  49 ### Options
  50
  51 - `--keep-threads` - Don't prune posts when they are part of a thread where at least one post has seen local interaction (e.g. one of the posts is a local post, or is favourited by a local user, or has been repeated by a local user...). It also wont delete posts when at least one of the posts in that thread is kept (e.g. because one of the posts has seen recent activity).
  52 - `--keep-non-public` - Keep non-public posts like DM's and followers-only, even if they are remote.
  53 - `--prune-orphaned-activities` - Also prune orphaned activities afterwards. Activities are things like Like, Create, Announce, Flag (aka reports)... They can significantly help reduce the database size.
  54 - `--vacuum` - Run `VACUUM FULL` after the objects are pruned. This should not be used on a regular basis, but is useful if your instance has been running for a long time before pruning.
  55
  56 ## Create a conversation for all existing DMs
  57
  58 Can be safely re-run
  59
  60 === "OTP"
  61
  62     ```sh
  63     ./bin/pleroma_ctl database bump_all_conversations
  64     ```
  65
  66 === "From Source"
  67
  68     ```sh
  69     mix pleroma.database bump_all_conversations
  70     ```
  71
  72 ## Remove duplicated items from following and update followers count for all users
  73
  74 === "OTP"
  75
  76     ```sh
  77     ./bin/pleroma_ctl database update_users_following_followers_counts
  78     ```
  79
  80 === "From Source"
  81
  82     ```sh
  83     mix pleroma.database update_users_following_followers_counts
  84     ```
  85
  86 ## Fix the pre-existing "likes" collections for all objects
  87
  88 === "OTP"
  89
  90     ```sh
  91     ./bin/pleroma_ctl database fix_likes_collections
  92     ```
  93
  94 === "From Source"
  95
  96     ```sh
  97     mix pleroma.database fix_likes_collections
  98     ```
  99
 100 ## Vacuum the database
 101
 102 !!! note
 103     By default Postgresql has an autovacuum deamon running. While the tasks described here can help in some cases, they shouldn't be needed on a regular basis. See [the Postgresql docs on vacuuming](https://www.postgresql.org/docs/current/sql-vacuum.html) for more information on this.
 104
 105 ### Analyze
 106
 107 Running an `analyze` vacuum job can improve performance by updating statistics used by the query planner. **It is safe to cancel this.**
 108
 109 === "OTP"
 110
 111     ```sh
 112     ./bin/pleroma_ctl database vacuum analyze
 113     ```
 114
 115 === "From Source"
 116
 117     ```sh
 118     mix pleroma.database vacuum analyze
 119     ```
 120
 121 ### Full
 122
 123 Running a `full` vacuum job rebuilds your entire database by reading all of the data and rewriting it into smaller
 124 and more compact files with an optimized layout. This process will take a long time and use additional disk space as
 125 it builds the files side-by-side the existing database files. It can make your database faster and use less disk space,
 126 but should only be run if necessary. **It is safe to cancel this.**
 127
 128 === "OTP"
 129
 130     ```sh
 131     ./bin/pleroma_ctl database vacuum full
 132     ```
 133
 134 === "From Source"
 135
 136     ```sh
 137     mix pleroma.database vacuum full
 138     ```
 139
 140 ## Add expiration to all local statuses
 141
 142 === "OTP"
 143
 144     ```sh
 145     ./bin/pleroma_ctl database ensure_expiration
 146     ```
 147
 148 === "From Source"
 149
 150     ```sh
 151     mix pleroma.database ensure_expiration
 152     ```
 153
 154 ## Change Text Search Configuration
 155
 156 Change `default_text_search_config` for database and (if necessary) text_search_config used in index, then rebuild index (it may take time).
 157
 158 === "OTP"
 159
 160     ```sh
 161     ./bin/pleroma_ctl database set_text_search_config english
 162     ```
 163
 164 === "From Source"
 165
 166     ```sh
 167     mix pleroma.database set_text_search_config english
 168     ```
 169
 170 See [PostgreSQL documentation](https://www.postgresql.org/docs/current/textsearch-configuration.html) and `docs/configuration/howto_search_cjk.md` for more detail.
 171
 172 ## Pruning old activities
 173
 174 Over time, transient `Delete` activities and `Tombstone` objects
 175 can accumulate in your database, inflating its size. This is not ideal.
 176 There is a periodic task to prune these transient objects,
 177 but on first run this may take a while on older instances to catch up
 178 to the current day.
 179
 180 === "OTP"
 181
 182     ```sh
 183     ./bin/pleroma_ctl database prune_task
 184     ```
 185
 186 === "From Source"
 187
 188     ```sh
 189     mix pleroma.database prune_task
 190     ```