"M" is for MySQL
Tags: codenewbie, sideprojects, security, todayilearned
Published: 2020-12-25 00:16:22

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](#L2) is a list of RDBMS that you might look at and link [3](#L3) is information on choosing a database.)

[:o:]Linux
[:o:]Apache
[:x:]__MySQL__
[:x:]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. <sup>[5](#L5)</sup> PostgreSQL became popular for its ACID-compliance (Atomicity, Consistency, Isolation, Durability) and support of both non-relational and relational data types.
![PostgreSQL](https://computingforgeeks.com/wp-content/uploads/2018/11/Install-PostgreSQL-11-Fedora-29-min.jpg)


# <a name="TOC">Table Of Contents</a>
[Step 1: Installing PostgreSQL](#S1)
[Step 2: Configuring PostgreSQL](#S2)
[Step 3: The Database](#S3)





# <a name="S1">Step 1:  Installing PostgreSQL</a>
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. <sup>[8](#L8)</sup>
 - `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 <sup>[7](#L7)</sup>

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](#TOC)}











# <a name="S2">Step 2: Configuring PostgreSQL</a>
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. <sup>[4](#L4)</sup>
- 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)." <sup>[6](#L6)</sup>
- "Tables are grouped into databases, and a collection of databases managed by a single PostgreSQL server instance constitutes a database _cluster_." <sup>[6](#L6)</sup>


__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`.![Alt Text](https://dev-to-uploads.s3.amazonaws.com/i/nl3evex4s46xpnybjo3z.png)
 - 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](#TOC)}









# <a name="S3">Step 3: The Database</a>
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](#TOC)}




<hr>

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

[:o:]Linux
[:o:]Apache
[:o:]__MySQL__
[:x:]PHP



<hr>

Happy Hacking
![Happy Hacking ^_^](https://media.giphy.com/media/l3vRmVv5P01I5NDAA/giphy.gif)

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



##### <figcaption>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.</figcaption>