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.)
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. 5 PostgreSQL became popular for its ACID-compliance (Atomicity, Consistency, Isolation, Durability) and support of both non-relational and relational data types.
Like other installs PostgreSQL is pretty straight forward to install.
sudo apt install postgresql postgresql-contrib libpqto install a PostgreSQL in the Ubuntu server along with some commonly used add-ons for it. 8
postgresqlis to install PostgreSQL
postgresql-contribis to install additional supplied modules specific to a package
- `libpq1 is to install libraries and headers for C language frontend development 7
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.
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. 4
- 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 --interactivecommand 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)." 6
- "Tables are grouped into databases, and a collection of databases managed by a single PostgreSQL server instance constitutes a database cluster." 6
Now that that is out of the way PostgreSQL needs to be configured.
- Switch over to the default Postgres account with
sudo -i -u postgresand notice that your terminal now shows "postgres@homeserver:$", or whatever you named your server, instead of "root@homeserver:$".
sudodoes need to be used for this command even if you are logged in as root already.
- Create a new Postgres specific user role with
- The "--interactive" flag will enable a prompt for the name and role you want to add.
sudo -u postgres createuser --interactivecommand can be run a root to do the same thing fyi.
- Create a database for your new PostgreSQL users to connect with
createdb danwhere "dan" is whatever name you chose for the precious step.
sudo -u postgres createdb dancommand can be run a root to do the same thing fyi.
- Now if you type
exitto leave the Postgres user role and type
sudo -i -u danto switch into the user you created, you should be able to interact with the database created with the
- To leave the
psqlcli interface type
- To leave the
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.
Success! Now that the Linux, Apache, and PostgreSQL are configured it's time to move onto PHP.