PostgreSQL for Lemmy instance installers/operators/upgraders
PostgreSQL for Lemmy instance installers/operators/upgraders
Diving in, I haven't worked with PostgreSQL for 15 years, but sharing random notes and obsrervations
pg_repack is a heavy duty extension for performance optimizaton: https://github.com/reorg/pg_repack
These instructions assume you installed "Lemmy from scratch" on Linux (as opposed to Docker, or FreeBSD instead of Linux, etc).
Linux shell on server of instance
Change users to the database Linux account:
sudo -iu postgres
Open the PostgreSQL shell client application:
psqlquery the database server about table locks
select * from pg_locks;from psql shell, list the lemmy_server tables
connect to the database named "lemmy":
\c lemmy
You are now connected to database "lemmy" as user "postgres".List the tables in the database:
\dtList of relations Schema | Name | Type | Owner --------+----------------------------+-------+------- public | __diesel_schema_migrations | table | lemmy public | activity | table | lemmy public | admin_purge_comment | table | lemmy public | admin_purge_community | table | lemmy public | admin_purge_person | table | lemmy public | admin_purge_post | table | lemmy public | comment | table | lemmy public | comment_aggregates | table | lemmy public | comment_like | table | lemmy public | comment_reply | table | lemmy public | comment_report | table | lemmy public | comment_saved | table | lemmy public | community | table | lemmy public | community_aggregates | table | lemmy public | community_block | table | lemmy public | community_follower | table | lemmy public | community_language | table | lemmy public | community_moderator | table | lemmy public | community_person_ban | table | lemmy public | email_verification | table | lemmy public | federation_allowlist | table | lemmy public | federation_blocklist | table | lemmy public | instance | table | lemmy public | language | table | lemmy public | local_site | table | lemmy public | local_site_rate_limit | table | lemmy public | local_user | table | lemmy public | local_user_language | table | lemmy public | mod_add | table | lemmy public | mod_add_community | table | lemmy public | mod_ban | table | lemmy public | mod_ban_from_community | table | lemmy public | mod_feature_post | table | lemmy public | mod_hide_community | table | lemmy public | mod_lock_post | table | lemmy public | mod_remove_comment | table | lemmy public | mod_remove_community | table | lemmy public | mod_remove_post | table | lemmy public | mod_transfer_community | table | lemmy public | password_reset_request | table | lemmy public | person | table | lemmy public | person_aggregates | table | lemmy public | person_ban | table | lemmy public | person_block | table | lemmy public | person_follower | table | lemmy public | person_mention | table | lemmy public | person_post_aggregates | table | lemmy public | post | table | lemmy public | post_aggregates | table | lemmy public | post_like | table | lemmy public | post_read | table | lemmy public | post_report | table | lemmy public | post_saved | table | lemmy public | private_message | table | lemmy public | private_message_report | table | lemmy public | registration_application | table | lemmy public | secret | table | lemmy public | site | table | lemmy public | site_aggregates | table | lemmy public | site_language | table | lemmy public | tagline | table | lemmy (61 rows)query to list Community joins that are pending
SELECT * FROM community_follower WHERE pending='t';This kind of query I'd like to work on adding to the server admin screens for operators.
query to list Communities by name
SELECT id,instance_id,name,title,local,published FROM community ORDER BY name;edit: tickle federation replication