Skip to content

Migration from Twitter to Mastodon

Many people (I don’t like this phrase) are leaving Twitter these days, and looking for a new social media home. One of these places is Mastodon. This blog post aims to summarize the steps necessary for a migration, and includes pointers to websites which can help with said move.

 

Image & CC: https://www.pexels.com/photo/3-grey-elephants-under-yellow-sky-68550/

 

What is Twitter?

Twitter: @ascherbaum

Twitter is a Microblogging service. Users post short texts with maximal 140 280 characters, optionally including media attachments. Tweets (that’s the name for the posting) are public by default, however Twitter implemented the ability to protect accounts (make the content private to followers only), or recently implemented functionality to target specific user groups for Tweets. In October 2022 Elon Musk completed the acquisition of Twitter, and took over as CEO. The following weeks have seen erratic and dramatic changes, which are not well-received by all users. Quite a number of users decided to leave Twitter. In addition, as a consequence of the turmoil some companies stopped doing advertisements on Twitter. The future will show if the users and advertisers will come back.

Users have a unique username on the platform, mine is @ascherbaum.

 

What is Mastodon?

 

Mastodon: @ascherbaum@mastodon.social

Mastodon is a Microblogging, which in contrast to the centralized Twitter, runs on decentralized (federated) instances (servers). The instances communicate with each other. The software is open source, and the project started around 2016.

Postings in Mastodon are named Toots, not Tweets. Or Trööt in German. Please let me know the word in other languages, I will update this posting.

After Elon Musk took over at Twitter, users started to migrate to Mastodon as an alternative, and every controversial announcement shows a new wave of users leaving. This will likely keep going for quite a while.

Mastodon users have a unique username on one instance, however the same username on a different instance can be used by someone else. There is no universal verification across instances, instances might implement their own verification. For example the social.bund.de instance is only open to other federal agencies of the German government - therefore every account on this instance is already validated as a government account.

My Mastodon account (the one I’m currently using) is ascherbaummastodon.social. The software allows users to move to new instances and migrate followers over, check the profile settings of your instance how to do that.
 

 

Continue reading "Migration from Twitter to Mastodon"

PGSQL Phriday #002: PostgreSQL Backup and Restore

Thanks to Ryan Booz we now have the #PGSQLPhriday blogging series. The second edition is about backups. And of course also about restore.

Backups? Do you need that?

If your data is not important, you certainly don't need to bother about backups. Then again, why do you even store the data in the first place?

For anyone else, especially everyone who runs business critical or mission critical applications, having a backup is important. Equally important, but sometimes ignored, is the ability to restore the backup.

The requirements for backups are manifold:

  • How quickly must you be able to restore the backup (SLA)?
  • Do you need to be able to restore every transaction (catch all changes) or is a snapshot (backup freuency, hourly, daily, weekly, monthly) sufficient?
  • How can the backup integrity be verified? 
  • Where do store the backup, and make sure that a disaster (as example: lost of data center) does not affect the backup?
  • Who can restore the backup, what is the plan for that?
  • ...

Your #PGSQLPhriday task

Describe how you do backups for your PostgreSQL databases.

Which tool(s) are you using, where do you store backups, how often do you do backups?
Are there any recommendations you can give the reader how to improve their backups?
Any lesser known features in your favorite backup tool?
Any new and cool features in a recently released version?

Bonus question: Is pg_dump a backup tool?

Continue reading "PGSQL Phriday #002: PostgreSQL Backup and Restore"

PGSQL Phriday #001: Two truths and a lie

Uh, what is this #PGSQLPhriday thing about? Everyone is writing blog posts about this today ... For details, please read the introduction from Ryan Booz.

This month's topic: pick your three favorite or most important best pratices for #PostgreSQL. Tell them in a form that two are true, one is a lie. See if you can spot which one is true and which one is false. The resolution is at the end of this blog posting.

Let's dive in: These days it's rare that I start with a fresh PostgreSQL installation which is not in any way already modified. Whenever we install a new database at work, it's already coming with a templated configuration, backup (if necessary: despite running many hundred databases not all of them need a backup), monitoring, and pre-configured access. Even the database on my private laptop is configured using Ansible. But that's a good example by itself: what are the steps I find important enough to have a Playbook for. My top three list:

  1. Change a few most important configuration parameters, then restart the database
  2. Use pg_dump for backups
  3. Create necessary accounts

Which one is the lie?

 

Continue reading "PGSQL Phriday #001: Two truths and a lie"

Changes to the public schema in PostgreSQL 15 and how to handle upgrades

In September 2021, a patch for the upcoming PostgreSQL version 15 was committed which introduces a visible change for users: the CREATE privilege for the public schema is no longer set by default. This is a recommendation from CVE-2018-1058.

What does that mean for the average (non-superuser) user?

In PostgreSQL 14:

postgres=# SELECT version();
                                                version                                                
-------------------------------------------------------------------------------------------------------
 PostgreSQL 14.5 on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 9.4.0-1ubuntu1~20.04.1) 9.4.0, 64-bit
(1 row)

postgres=# CREATE ROLE unprivileged WITH LOGIN;
CREATE ROLE

postgres=# CREATE DATABASE priv_test;
CREATE DATABASE

postgres=# \c priv_test 
You are now connected to database "priv_test" as user "ads".

priv_test=# \dn+ public
                       List of schemas
  Name  | Owner | Access privileges |      Description       
--------+-------+-------------------+------------------------
 public | ads   | ads=UC/ads       +| standard public schema
        |       | =UC/ads           | 
(1 row)

We see that PUBLIC (the second line in Access privileges) has USAGE (U) and CREATE (C) privileges. A regular user can create a table in the public schema:

priv_test=# SET SESSION ROLE unprivileged;
SET
priv_test=> SHOW search_path;
   search_path   
-----------------
 "$user", public
(1 row)

priv_test=> CREATE TABLE priv_test (id INT);
CREATE TABLE
priv_test=> \dp priv_test 
                               Access privileges
 Schema |   Name    | Type  | Access privileges | Column privileges | Policies 
--------+-----------+-------+-------------------+-------------------+----------
 public | priv_test | table |                   |                   | 
(1 row)

 

This is how the public schema privileges look in PostgreSQL 15:

postgres=# SELECT version();
                                                 version                                                  
----------------------------------------------------------------------------------------------------------
 PostgreSQL 15beta3 on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 9.4.0-1ubuntu1~20.04.1) 9.4.0, 64-bit
(1 row)

postgres=# CREATE ROLE unprivileged WITH LOGIN;
CREATE ROLE

postgres=# CREATE DATABASE priv_test;
CREATE DATABASE

postgres=# \c priv_test 
You are now connected to database "priv_test" as user "ads".

priv_test=# \dn+ public
                                       List of schemas
  Name  |       Owner       |           Access privileges            |      Description       
--------+-------------------+----------------------------------------+------------------------
 public | pg_database_owner | pg_database_owner=UC/pg_database_owner+| standard public schema
        |                   | =U/pg_database_owner                   | 
(1 row)

The C (CREATE) is missing. And the CREATE TABLE for a regular user is no longer working by default:

priv_test=# SET SESSION ROLE unprivileged;
SET
priv_test=> SHOW search_path;
   search_path   
-----------------
 "$user", public
(1 row)

priv_test=> CREATE TABLE priv_test (id INT);
ERROR:  permission denied for schema public
LINE 1: CREATE TABLE priv_test (id INT);

But how is this change handled in upgrades?

 

 

Continue reading "Changes to the public schema in PostgreSQL 15 and how to handle upgrades"

2 + 2 * 4 = 16?

Jens Kubieziel recently posted a Twitter poll: 2 + 2 * 4

The available options:

  • 16
  • 15
  • 14
  • 13

Notably absent is the correct result (at least for our ordinary mathematics). This poll is a No-Win Situation: you can't win, because the correct result is not available as an option.

Nevertheless I had an idea, but for this I had to wait until the poll closes.

2 + 2 <strong /> 4 = 16

 

Continue reading "2 + 2 * 4 = 16?"

ctid and other PostgreSQL table internals

While attending a conference (yay) the discussion turned to system columns, and especially ctid. This was in context of backups, and the physical position of data on disk. The question was if that can be used to determine if an incremental backup is required, and if it is enough to just copy that field where the ctid points to. Not quite, but I’d like to use this question to describe the system columns more in detail.

In a PostgreSQL table one may find - depending on the server version - the following additional system columns:

  • tableoid
  • xmin
  • xmax
  • cmin
  • cmax
  • ctid
  • oid

Most of the columns exist in every table, but are excluded when doing a SELECT *. However one can include the column(s) in a query, and then PostgreSQL will return the values for the columns as well.

 

 

Continue reading "ctid and other PostgreSQL table internals"

PostgreSQL Upgrades are hard!

Together with Lætitia Avrot and Nikolay Samokhvalov I was invited to participate in a Community Panel (YouTube video) about PostgreSQL Upgradability at Postgres Vision 2022. The panel was moderated by Bruce Momjian and initiated and organized by Jimmy Angelakos. Bruce did talk with each of us before, which helped a lot to guide the discussion in the right direction. The recording of the panel discussion is available on the Postgres Vision website.

During this panel each of us provided examples for how easy or complicated PostgreSQL upgrades still are.
 

 

Continue reading "PostgreSQL Upgrades are hard!"

PostgreSQL Project @ GSoC 2022

As is a good tradition, the PostgreSQL Project participates in Google Summer of Code (GSoC). Last year we submitted 7 projects for 7 students - and got all 7 projects accepted. This year we got quite a few more good proposals from students, and more mentors are helping. Guess what? Google accepted all 12 proposals!

Google modified the program again. For 2021 they cut the time for every project in half, to accommodate for the at-home work during the Covid-19 pandemic. This turned out to be suboptimal, and many larger projects need more time. This year students can choose between “medium” (175 hours) and “large” (350 hours) projects. This gives everyone a chance to scope the project accordingly.

 

 

Continue reading "PostgreSQL Project @ GSoC 2022"