So, next we have Boris Mahihaas with trusted Postgres architect, deploying Postgres with
infrastructure as code.
Right, so thank you very much.
Thanks for coming.
My name, as she says, Boris Mahihaas.
I used to be a solutions architect at ADB, but I grew a little bit of white hairs around
senior solution architects, but this pieces off a lot of the building architects.
So actually my real title is holistic system software engineer, because I would like to
see the things from the fundamental interconnectedness of all things.
I used to be a developer, operational people, a DBA consultant, so I'd like to see the whole
stuff.
That's why I see the value of the DevOps philosophy, because it's trying to get the
whole thing kind of a one thing of deploying stuff in a more reliable way.
Apart from that, I'm also an air guitar player, and I really love metal music, and within
other type of music.
I'm going to talk about trusted Postgres architect.
So here, who uses already Postgres here in the room?
Nice, okay, that's very good.
Okay, so who didn't raise the hand wants to use Postgres maybe, but I think everybody
already raised the hand.
That's good.
Okay, there you go.
Thank you.
So this talk is for you.
All the rest is also an interesting problem, this, because it's about reliably developing
Postgres in multiple different infrastructures.
Okay, so this is a use case.
This is a developer.
She is trying to develop a new project.
She wants to use Postgres finally because it's being one of the most popular databases
in the last years, and then she has this brilliant idea, but she doesn't want to start using
single commands all the time because she wants to have an environment where she can test,
test, test, and when everything is working finally well, she's going to be able to deploy
that into different environments, either test environment, pre-production, staging, whatever
you call it, but exactly the same thing.
So the typical stuff that people do is like, I have a container, I'm going to put that
specific container into the server.
This is not exactly that, but it can also be relying on containers in order to emulate
the final architecture.
So let me explain a little bit more.
So you want to do it in a reliable way, and that's why the name of the tool is called
trusted Postgres architect, which is the tool, TPA.
We like to call it TPA because it gets people confused with TAP, which sounds like tap, which
is for you to get your favorite beverage at the bar.
The first goal that she has is to deploy one single instance running Postgres 16.
This could be also if you are running already Postgres 14 or 15, you want to try the new
features of Postgres 16.
Who is already running Postgres 16 here in the room?
Okay, so much less than the people who was already using Postgres.
So this is probably one way for you to test the new version.
So I'm going to just show you code here, which is YAML.
You might not like it, but it is the standard way of doing Ansible stuff or doing deployment.
So in this whole screen, which is pretty large, I'm going to put all the code that you need
in order to have this one single instance.
First of all, in TPA, you have to specify your architecture.
This is a master one.
I know now we call it primary, but master still sounds nice because it reminds me about
master, master.
So that's why it's called master one.
And obviously it's going to be called, FOSTA, is the name of the cluster.
Then you can have cluster variables, plenty of stuff that you can ignore at the moment.
I'm going to come back to one of them afterwards.
But the most relevant here is this one, Postgres version 16.
That's what you need.
Okay.
So this is the version you want then, and it's going to put you that version deployed.
So this is the cluster variables.
I'm going to come back to the cluster variables later.
Then because you want to be able to do deployments in multiple locations for fault tolerance,
high availability, it's always good to specify a location.
We are in Brussels, so we are going to call location Brussels.
And we are going to have an instance, obviously.
Thank you.
At the ULB, but first we're going to say which type of instance and which default we have.
So we are going to do it with a Debian image.
It's going to be a specific detailer by TPA, but you can use whatever image you want here.
And here the platform, it says that it's Docker.
This is not cloud native stuff.
It's really an easy way to have a virtual machine that I can connect to it and try to behave
as if it is a virtual machine, but it's a container with everything that you need there
for a virtual machine.
And as you can see, TPA uses Ansible.
So we are going to have this Ansible user here for connecting to the machines.
And here is the instance.
You specify only these parameters, the name, the location.
This is a number within your cluster and the role.
And the role is to be a primary.
So here we use the most modern way of referring to the primary node.
That's it.
That's all the code that you need for one single instance, of course.
Then because this is Ansible, you do TPX-SEC.
This is the executable of the trusted architecture.
Provision, so you provision your cluster and then deploy.
And then you get it.
Okay?
So how do you connect to it?
Yeah?
Well, I told you that the Docker containers are going to behave as a virtual machine,
so we can SSH to the machine.
So we do SSH using that file that is going to be generated in the process of doing the
provision.
ULB is the alias that we gave to the instance.
And then we do the typical thing.
I become user Postgres.
Oops.
I become user Postgres and I execute PSQL.
Yeah?
So it's really nice because it's using the super user Postgres and you want to have
this for applications.
So let's get a new requirement.
But this is how you connect, okay?
You want to have an administrator, which is not the Postgres user.
So we are going to call it Slonic because that's the name of the blue elephant.
Yeah?
And this is going to be an administrator.
And then you have Ada Lobleys, which is going to be the application user.
And we don't want to use the Postgres database, so we are going to have a FOSMDB, which is
owned by the application user.
So this gives us a little bit of more security already.
So how do you change the previous code in order to allow this new request?
So in the cluster variables, we are going to just keep these two variables there, the
failover manager, which I'm going to use later on, and the Postgres version.
And then I'm going to add the users.
Yeah?
So this is how you add the user.
You say the username.
I'm going to ask TPA to also generate a password for that one.
And the roles of that particular user, in this case, is going to be super user.
That's the administrator.
You can also grant permissions and stuff like that.
But in this case, I want to have a role attribute.
And then we have the developer, which is doing the application part, Ada Lobleys.
We just got the password for that one.
Then we ask for the database.
We give the name and the owner.
And that's it.
So I'm adding new stuff.
So it's not just for the first deployment.
It's also for maintenance.
Okay?
So you can do a git commit of your new version of the stuff.
So you can keep a track of your infrastructure with different versions.
If you want to revert this, you can also do it.
Right?
Then, of course, provision and deploy.
And you can continue.
Now I show you that you can connect to the database through SSH and then PSQL.
Now we wanted to do it with an application.
So what we are going to do is that we are going to ask for TPA.
Give me the password that you generated for this cluster for that particular user.
The password is a random stuff, which is not that random.
It actually contains a reference to a metal band from Belgium.
If you can figure it out, I will pay you a drink from it.
And then using that password, you can connect with the normal PSQL.
You provide the host IP, the port, the user.
And you put the minus capital W user that you can put that password there if you don't
want to put that in the PGE pass file, for instance.
But now I'm not using the SSH stuff.
So now I'm really behaving as if it is an application.
Okay?
You can take a picture and try to figure out the reference.
So we have this now with that little amount of code, but we don't have any fault tolerance
yet.
What happens if this thing crashes?
Well, we want to have a replica, exactly the same version, physical replication, and
that's the new thing that we are going to do.
So let's take the code again.
We have that cluster variables for the failover manager that says absolutely nothing running
Poster 16.
GPA can do it with a tool called Rep Manager, which I like a lot, and also Patroni, which
is also very, very good stuff.
So you can choose.
In this case, I'm choosing for Rep Manager.
And then in the instances, if you remember, I have this primary one.
The only thing that I need to do is to add another instance.
This one is the VUB.
So you see the French-picking one, the Dutch-picking one, but the city is in English so that nobody
complains about the one that it picked.
Now you have this one.
It's a different role.
You see this is a replica.
And this is the primary.
So I have to say who is the upstream of this replica and is the ULB.
And I have a cluster with two nodes.
Again, TPXSek provision, TPXSek deploy.
Let's continue.
I want to have more fault tolerance.
What happens if there is an attack in Brussels and the old universities get destroyed?
We want to have a third replica, but we don't want to do it replicating from the primary.
We want to have cascading replication.
But if somebody deletes a table here by accident, it's also going to be deleted in all the nodes.
So you need to have some backup and restore for point-in-time recovery.
That's why you want to have in another part of the country your barman, because you trust
your barman, which is for backup and recovery management.
It's important that your backups can be recovered.
If you just have backup but you never recover them, you don't have backups, basically.
So this is what we are going to build now.
Let's get back to what we have.
We have the location Brussels and two instances.
Let's add another location, Vlonderen.
This is in the north.
And then we are going to add a replica in a very nice place called Achel.
Used to be a trapeze beer.
Not anymore.
It's still a very good beer, but it's no longer a trapeze.
This is just for your common knowledge about Foslin.
So then you get the location is Vlonderen.
I'm going to say that it's also a replica, and I'm going to have the view be a upstream.
This is how I build cascading replication.
I could do now provision and deploy, and I have my older replica, but I also want to
have backup and recovery.
So what I do is I add here another location, which is Wallouni.
And then I add a very nice place, which is still a trapeze cheese, also beer.
This is my favorite one, actually.
And then look at the role.
It is Barman.
So here's how it gets a backup and recovery management just by adding this.
So it's an instance with a Barman role.
Now where am I taking the backup from?
Well, I need some space there.
I didn't put it on the bottom because otherwise you wouldn't read it.
So it is coming here.
You just put backup, rush for, and that's how you build it.
So this is all the code you need, and you have already a cluster with cascading replication
and backup and recovery tool.
Good.
You do provision and deploy, and then you're done, and you have built an architecture.
So this is all done with Docker containers.
The idea is that you can take exactly the same file and put it into virtual machines
and other stuff.
So if you don't remember how to do the configuration files, it's very easy.
You can also use the tool TPXSecConfigure, the cluster for them, and you can say, I want
to use this architecture, running PostgreSQL version 16.
The platform is going to be Docker.
My operating system is Debian, and the failover manager is RepManager.
And you get something very similar that you just need to change some names.
Now, look at the Docker thing here.
If I change it to Bear, because I like Bear Metal, you just change that and you get a
different configuration file with some IP addresses that you just need to add.
It's basically the same.
But you can also deploy to AWS.
So TPA is going to also create your virtual machines at AWS if you have the credentials,
and it's going to manage all the network things.
So you just have to do provision and deploy, and you get everything.
It's super cool.
Okay, so configure.
You provide architecture, the platform, and the OS.
Then you do provision, and then you do deploy.
And then deploy also provides some hooks, like doing pre-deploy, pre-NDB, post-deploy.
You can have some stuff like enhancing your cluster.
So to summarize, you have an architecture here, and an executor of the executor, which
is of the architecture, which is the orchestrator here, and it's going to deploy to some machines.
This machine can be running on virtual machines in AWS if you have the credentials, or it
can be Bear Metal, or it can be Docker containers.
When I see a ship like this with containers, I always think about the Albatross and the
rhyme of the ancient mariners.
Okay, so for those people who also listen the same kind of music, you know what I'm talking
about.
The cool thing is that it is exactly the same architecture here, and exactly the same way
of doing the provision and deploy.
It's just a different target.
So instead of submitting your container to somewhere else, you say, I'm going to deploy
the same architecture somewhere else.
So what we basically do is when we do a project with a customer who wants to run an architecture,
we deploy that with using TPA, the definition, and then we pass to the support team, which
is going to continue talking with the customer after we have finished the project, exactly
the same architecture, but in Docker containers.
So whenever the people who is having the project in production has an issue, contact support
and say, like, I have an issue with my architecture, they can deploy a model of it with Docker
containers, and then they can test the whole thing there.
So it gives you really a continuation of your project.
It's not just the first deployment that is easy, but it's also the maintenance and the
documentation of it.
You don't want to document everything on PDF.
You want to document in code.
So your configuration file is the documentation of your architecture because you are using
your infrastructure as code.
That's the main advantage of using this kind of tool.
That's why I like it a lot.
All right.
So these are the platforms.
If you want to have a look at it, it is in GitHub now.
It is released with a GPL version three.
It is recently been open source, but this tool, we have been using it for six years already.
So it's quite mature and have our best practices.
Everything is done with security layers, SSL, host-based authentication, everything
is done for you.
And you have the documentation also at enterprisedba.com.
To include, it is infrastructure as code.
We always put them in Git so that we can have different versions.
We know how it is evolving our infrastructure.
It is not good only for testing because you can test your entire infrastructure, but you
can also use it for deployment in production afterwards.
It is a way of documenting your deployment, not just PDF, but documented as code.
And it's not just for the deployment, but it's also for the maintenance of your stuff.
And finally, we get it open source.
It's been there for a while.
We have been using it.
We have been fighting for getting open source and you get it there.
So you are free to use it as much as you want.
Now all the documentation is there, but you can also contact me via my personal email,
company email, also mastodon.
And that part is also for the other social media with full of haters.
And Hale Slonic.
Thank you very much.