"M" is for MySQL

When I last left off I had just finished the Apache HTTP server install and basic configuration so now it's time for the "M" in LAMP, MySql PostgreSQL. The "M" in the LAMP stack technically represents "MySQL", an open-source relational database management system (RDBMS), but in recent years has expanded to include any RDBMS that fits your needs; such as MariaDB or PostgreSQL, or even NoSQL databases such as MongoDB.

For my "M" I chose to use PostgreSQL because it has a strong reputation for its reliability, flexibility, support of open technical standards, and its use of both non-relational and relational data types. That being said, there are a lot of options for databases anymore so it's important to do your own research and find one that you are comfortable with and does what you need. (Reference link 2 is a list of RDBMS that you might look at and link 3 is information on choosing a database.)

[⭕]Linux
[⭕]Apache
[❌]MySQL
[❌]PHP

PostgreSQL is an open source Object-RDMBS (Relational Database Management Systems) that uses a client/server model. As with other client/server applications, the client and server can be on different hosts, or the same host, and they communicate over a TCP/IP network connection. ⁵ PostgreSQL became popular for its ACID-compliance (Atomicity, Consistency, Isolation, Durability) and support of both non-relational and relational data types.

Table Of Contents

Step 1: Installing PostgreSQL
Step 2: Configuring PostgreSQL
Step 3: The Database

Step 1: Installing PostgreSQL

Like other installs PostgreSQL is pretty straight forward to install.

1. Run sudo apt install postgresql postgresql-contrib libpq to install a PostgreSQL in the Ubuntu server along with some commonly used add-ons for it. ⁸
   • postgresql is to install PostgreSQL
   • postgresql-contrib is to install additional supplied modules specific to a package
   • `libpq1 is to install libraries and headers for C language frontend development ⁷

The install is seriously that easy and if you want to see a list of additional addon package flags available run apt-cache search postgres. I recommend adding on > list.txt to the end of the search command to export the list to a file called "list.txt" so that you can actually read over it.

{Back to the Table Of Contents}

Step 2: Configuring PostgreSQL

At this point there are a few things that are good to know about Postgres:

• Postgres uses a concept called “roles” to handle authentication/authorization and upon installation, Postgres uses the "ident authentication" for any connections from the same machine. This means that it associates Postgres roles with a matching Unix/Linux system account and if a role exists within Postgres, a Unix/Linux username with the same name is able to sign in as that role. ⁴
• Postgres authentication system assumes that for any role used to log into it, that role will have a database with the same name that it can access. This means that if a user is created with the sudo -u postgres createuser --interactive command called "dan", that the role will attempt to connect to a database which is also called “dan” by default and if this database isn't created then it needs to be for that user role to interact with Postgres properly.
• "Each table is a named collection of rows. Each row of a given table has the same set of named columns, and each column is of a specific data type. Whereas columns have a fixed order in each row, it is important to remember that SQL does not guarantee the order of the rows within the table in any way (although they can be explicitly sorted for display)." ⁶
• "Tables are grouped into databases, and a collection of databases managed by a single PostgreSQL server instance constitutes a database cluster." ⁶

Now that that is out of the way PostgreSQL needs to be configured.

1. Switch over to the default Postgres account with sudo -i -u postgres and notice that your terminal now shows "postgres@homeserver:$", or whatever you named your server, instead of "root@homeserver:$".
   • The sudo does need to be used for this command even if you are logged in as root already.
2. Create a new Postgres specific user role with createuser --interactive.
   • The "--interactive" flag will enable a prompt for the name and role you want to add.
   • The sudo -u postgres createuser --interactive command can be run a root to do the same thing fyi.
3. Create a database for your new PostgreSQL users to connect with createdb dan where "dan" is whatever name you chose for the precious step.
   • The sudo -u postgres createdb dan command can be run a root to do the same thing fyi.
4. Now if you type exit to leave the Postgres user role and type sudo -i -u dan to switch into the user you created, you should be able to interact with the database created with the psql command.
   • To leave the psql cli interface type \q.

{Back to the Table Of Contents}

Step 3: The Database

Here I am going to skip setting up the database as I will be using my API backend to configure a specific database for it. At the time of writing this my database is being hosted on Heroku so I can test that my frontend works properly and I will move it into the server later in this series.

{Back to the Table Of Contents}

---

Success! Now that the Linux, Apache, and PostgreSQL are configured it's time to move onto PHP.

[⭕]Linux
[⭕]Apache
[⭕]MySQL
[❌]PHP

---

Happy Hacking

Resources:

1. https://en.wikipedia.org/wiki/LAMP_(software_bundle)
2. https://en.wikipedia.org/wiki/List_of_relational_database_management_systems
3. https://www.ibm.com/cloud/blog/how-to-choose-a-database-on-ibm-cloud
4. https://www.digitalocean.com/community/tutorials/how-to-install-and-use-postgresql-on-ubuntu-20-04
5. https://www.postgresql.org/docs/7.4/tutorial-arch.html
6. https://www.postgresql.org/docs/7.4/tutorial-concepts.html
7. https://www.postgresql.org/download/linux/ubuntu/
8. https://gorails.com/deploy/ubuntu/20.04#database
9. https://stackabuse.com/using-postgresql-with-nodejs-and-node-postgres/

Please Note: that I am still learning and if something that I have stated is incorrect please let me know. I would love to learn more about what I may not understand fully.