Diving in, I haven’t worked with PostgreSQL for 15 years, but sharing random notes and obsrervations

  • RoundSparrow@lemmy.mlOPM
    link
    fedilink
    arrow-up
    0
    ·
    edit-2
    1 year ago

    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:
    psql

    query the database server about table locks

    select * from pg_locks;

    • RoundSparrow@lemmy.mlOPM
      link
      fedilink
      arrow-up
      0
      ·
      1 year ago

      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:
      \dt

                        List 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)
      
      
      • RoundSparrow@lemmy.mlOPM
        link
        fedilink
        arrow-up
        1
        ·
        edit-2
        1 year ago

        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

        • snoweA
          link
          fedilink
          arrow-up
          1
          ·
          edit-2
          1 year ago

          why would community joins be pending?

          edit: Ah I see. A better query might be:

          SELECT p.name, c.name, i.domain, p.local, * FROM community_follower
                   inner join person p on p.id = community_follower.person_id
                   inner join community c on c.id = community_follower.community_id
                   inner join instance i on c.instance_id = i.id
                   WHERE pending='t';
          

          which will show you the user, the community they’re trying to join, and the instance they’re trying to join on. Example:

          (redacted my users in case they don’t want it known what they’re trying to join.

          • RoundSparrow@lemmy.mlOPM
            link
            fedilink
            arrow-up
            0
            ·
            edit-2
            1 year ago

            Thank you for sharing the query.

            why would community joins be pending?

            I believe it is a glaring symptom of federation replication failure. Data isn’t making it back from the remote server to confirm the join. Either the outbound never made it to the remote, or the remote never made it back to your server. Multiple instances have had users complaining of these federation failures, example: https://lemmy.ml/post/1280517

            You (the end-user) can try to cancel the join of the community and join again to trigger new connection to the server. I would also add the date to the output so you can try to see when these failures are happening (are they all on the same day?)

            What software are you using to view the queries?

            • snowe@lemmy.ml
              link
              fedilink
              arrow-up
              1
              ·
              1 year ago

              What software are you using to view the queries?

              I use DataGrip. It’s fantastic.

              I believe it is a glaring symptom of federation replication failure. Data isn’t making it back from the remote server to confirm the join. Either the outbound never made it to the remote, or the remote never made it back to your server. Multiple instances have had users complaining of these federation failures, example: https://lemmy.ml/post/1280517

              You (the end-user) can try to cancel the join of the community and join again to trigger new connection to the server. I would also add the date to the output so you can try to see when these failures are happening (are they all on the same day?)

              Yeah sorry, I didn’t realize you meant remote community joins. I have had that problem in fact I still can’t join several communities due to that issue.