|
|
| (5 intermediate revisions by the same user not shown) |
| Line 1: |
Line 1: |
| − | https://www.postgresql.org/
| |
| | | | |
| − | https://en.wikipedia.org/wiki/PostgreSQL
| |
| − |
| |
| − | PostgreSQL, often simply Postgres, is an object-relational database management system (ORDBMS) with an emphasis on extensibility and standards compliance.
| |
| − |
| |
| − |
| |
| − | <br />
| |
| − | ==Deploying PostgreSQL on a Docker Container==
| |
| − |
| |
| − |
| |
| − | <br />
| |
| − | ===Install Docker CE on Ubuntu===
| |
| − | Docker is a technology that allows us to run containers. A container is a self-contained environment. A container can contain aspecific operating system, software packages, and configuration.
| |
| − |
| |
| − | We are going to install the Docker community edition (AKA Docker CE).
| |
| − |
| |
| − | https://store.docker.com/search?type=edition&offering=community
| |
| − |
| |
| − | https://docs.docker.com/install/linux/docker-ce/ubuntu/
| |
| − |
| |
| − | Please note that you will be required to create an account. After installing Docker, you might have to restart your computer.
| |
| − |
| |
| − |
| |
| − | <br />
| |
| − | ===Postgres docker installation===
| |
| − | After installing Docker, you are going to need to download the Docker image for Postgres 9.5. You can do this using the following command:
| |
| − |
| |
| − | sudo docker pull postgres:9.5
| |
| − |
| |
| − | You can then use the following command to see the list of all the Docker images installed on your computer:
| |
| − |
| |
| − | sudo docker images
| |
| − |
| |
| − | '''Setting environment variables:'''
| |
| − | In Linux and MacIn Mac and Linux we can use the following commands in Bash to create environment variables:
| |
| − | <syntaxhighlight lang="shell">
| |
| − | export DATABASE_USER=postgres
| |
| − | export DATABASE_PASSWORD=secret
| |
| − | export DATABASE_HOST=localhost
| |
| − | export DATABASE_PORT=5432
| |
| − | export DATABASE_DB=demo
| |
| − | </syntaxhighlight>
| |
| − |
| |
| − | The Postgres 9.5 image should be displayed in the list. At this point, we need to run a Docker container. Each container is a running instance of an image. We can have multiple containers running of a given image, but this time we will only run onecontainer because we don't need two instances of Postgres. We can create a Docker container with the Postgres image using thefollowing command:
| |
| − |
| |
| − | sudo docker run --name POSTGRES_USER -p "$DATABASE_PORT":"$DATABASE_PORT" -e POSTGRES_PASSWORD="$DATABASE_PASSWORD" -e POSTGRES_USER="$DATABASE_USER" -e POSTGRES_DB="$DATABASE_DB" -d postgres:9.5
| |
| − |
| |
| − |
| |
| − | <br />
| |
| − | ===Docker basics===
| |
| − | We have already learned how to download Docker images using the «docker pull» command and how to create a container using the «docker run» command. We will now learn about other basic Docker commands. We can use the «docker ps» command to see all the containers in our machine:
| |
| − |
| |
| − | sudo docker ps -a
| |
| − |
| |
| − | We should be able to see the Postgres container running.
| |
| − |
| |
| − | [[File:Output_of_the_dockerpa-a_command.png|1000px|thumb|center|Output of the docker pa -a command]]
| |
| − |
| |
| − | Each container and image has an associated ID that looks like the following:
| |
| − | c6f7dfc1a4c8
| |
| − |
| |
| − | We can use the following command to stop a running Docker container:
| |
| − | sudo docker stop INSERT_CONTAINER_ID_HERE
| |
| − | sudo docker stop c6f7dfc1a4c8
| |
| − |
| |
| − | Similarly, we can run a Docker container using the start command:
| |
| − | sudo docker start c6f7dfc1a4c8
| |
| − |
| |
| − | Please note that the run command is used to create a new Docker container while the start command is used to run an existingDocker container given its ID.
| |
| − |
| |
| − | We can use the following command to remove a Docker container:
| |
| − | sudo docker rm c6f7dfc1a4c8
| |
| − |
| |
| − | Please note that before you can remove a Docker container, you must stop it. The following command can be used to remove aDocker image:
| |
| − | sudo docker rmi INSERT_IMAGE_ID_HERE
| |
| − |
| |
| − | Please note that before you can remove a Docker image, you must stop and remove all its associated containers.
| |
| − |
| |
| − |
| |
| − | <br />
| |
| − | ==Classic PostgreSQL installation==
| |
| − | https://tecadmin.net/install-postgresql-server-on-ubuntu/
| |
| − |
| |
| − |
| |
| − | <br />
| |
| − |
| |
| − | ==Connecting to Postgres from SQLectron==
| |
| − | After starting a Postgres Docker container, we are going to try to connect to it from an external tool to make sure that everything isOK. The tool that we are going to use is known as SQLectron. You can download SQLectron from:
| |
| − |
| |
| − | https://github.com/sqlectron/sqlectron-gui/releases/tag/v1.30.0
| |
| − |
| |
| − | Sólo hay que descargar el archivo correspondientes (Sqlectron-1.30.0.tar.gz para Linux) y ejecutar (no requiere instalación):
| |
| − | ./Sqlectron-1.30.0/sqlectron
| |
| − |
| |
| − | After installing SQLectron and opening it. We need to create a new database connection using the add button:
| |
| − |
| |
| − | [[File:The_SQLectron_home_page.png|750px|thumb|center|The SQLectron home page]]
| |
| − |
| |
| − | A form will be displayed on the screen. We need to use the same details that we used in our environment variables and click test to check that they work. We will have a problem because we have set the database name environment variable DATABASE_DB as «demo», and by default, Postgres doesn't have a database called demo
| |
| − |
| |
| − | Please note that Postgres is going to be our database server but a database server can contain multiple database instances. Bydefault Postgres contains a database instance named postgres so we will use the default database to connect to the database server for the very first time:
| |
| − |
| |
| − | [[File:Creating_a_connection_in_SQLectron.png|750px|thumb|center|Creating a connection in SQLectron]]
| |
| − |
| |
| − | We can click on the test button to check that we can connect. Please note that the Docker Postgres container must be already running as explained in the preceding section. Once you manage to see Successfully connected you can click on save. After saving the new saved connection should be displayed on the screen:
| |
| − |
| |
| − | [[File:A_saved_connection_in_SQLectron.png|600px|thumb|center|A saved connection in SQLectron]]
| |
| − |
| |
| − | We need to click on the new database connection to open a new SQL editor. We can then write by hand an SQL statement to create the "demo" database:
| |
| − |
| |
| − | CREATE DATABASE demo;
| |
| − |
| |
| − | [[File:SQL_editor_in_SQLectron.png|750px|thumb|center|SQL editor in SQLectron]]
| |
| − |
| |
| − | After executing the preceding command, we need to disconnect from the database using the top right icon. We need to the return to the screen that allows us to select an existing connection:
| |
| − |
| |
| − | This time instead of opening the connection we need to edit it. We are going to update the connection details to use the demo database instead of the postgres database.
| |
| − |
| |
| − | Once more we can use the "test" button to ensure that everything is correct. If you cannot connect you should first ensure that the Docker container is running using the «docker ps -a» command. If everything is correct, the status column should show how long the container has been running.
| |
| − |
| |
| − |
| |
| − | <br />
| |
| − | ==PostgreSQL basics==
| |
| − |
| |
| − |
| |
| − | <br />
| |
| − | ===Connect to PostgreSQL===
| |
| − | https://tecadmin.net/install-postgresql-server-on-ubuntu/
| |
| − |
| |
| − | sudo su - postgres
| |
| − | psql
| |
| − |
| |
| − |
| |
| − | <br />
| |
| − | ===Defauld password===
| |
| − | https://www.liquidweb.com/kb/what-is-the-default-password-for-postgresql/
| |
| − |
| |
| − |
| |
| − | <br />
| |
| − | ===Set password===
| |
| − | https://www.2ndquadrant.com/en/blog/how-to-safely-change-the-postgres-user-password-via-psql/
| |
| − |
| |
| − | https://enterprise.arcgis.com/en/server/10.3/cloud/amazon/change-default-database-passwords-on-linux.htm
| |
| − |
| |
| − |
| |
| − | sudo su - postgres
| |
| − | psql
| |
| − | \password <username>
| |
| − | \password postgres # for the «postgres» user
| |
| − |
| |
| − |
| |
| − | <br />
| |
| − | ===List Databases and Tables from command line===
| |
| − | https://chartio.com/resources/tutorials/how-to-list-databases-and-tables-in-postgresql-using-psql/
| |
| − |
| |
| − |
| |
| − | <br />
| |
| − | ===Create Users and Databases from command line===
| |
| − | https://www.a2hosting.com/kb/developer-corner/postgresql/managing-postgresql-databases-and-users-from-the-command-line
| |
| − |
| |
| − | sudo su - postgres
| |
| − |
| |
| − | To create a database:
| |
| − | createdb -O user dbname
| |
| − | If we want to create the «twitterclone_db» that belog to the user «postgres»:
| |
| − | createdb -O postgres twitterclone_db
| |
| − |
| |
| − |
| |
| − | <br />
| |
