PostgreSQL on azure.cn: Installation deployment

PostgreSQL is an object-relational database (database management system) based on the BSD license. Because of the flexibility of the license, anyone can use, modify, and distribute PostgreSQL for free for any purpose. Simply put, even if you take it to change it, and then take it out and sell it, no one will be in control. Therefore, a lot of domestic database is based on PostgreSQL development.

There is a very interesting thing, PostgreSQL is not read "Post-gre-sequel", PostgreSQL developers read it as "post-gress-q-l", usually simply read as "Postgres".

PostgreSQL supports most SQL standards and offers many modern features, such as complex queries, foreign keys, triggers, views, transactional integrity, and multiple versioning concurrency controls. Similarly, PostgreSQL can be extended in many ways, such as by adding new data types, functions, operators, aggregation functions, indexing methods, and procedural languages. and support a lot of distributed cluster software, such as Pgpool, Pgcluster, Slony, Plploxy and so on, so it is easy to do read and write separation, load balancing, data level split.

Because Postgres has many benefits, many users now want to use PostgreSQL as a database service on Azure as well. On azure.com, PostgreSQL has already provided previews as a PAAs service for Azure.

However, there is currently no PAAs service available for PostgreSQL on azure.cn. What if the user wants to use it? Users are currently advised to build themselves on azure. In fact, the entire installation of the deployment process is not complicated, you can refer to the following steps to install themselves.

The installation environment is CentOS7.3, which is also a popular Linux system on azure.cn.

Before formally starting the installation, make sure that the dependencies required by PostgreSQL are properly installed and that we install all of the dependent packages with a single command:

# yum Install readline-devel gcc make zlib-devel OpenSSL openssl-devel libxml2-devel pam-devel Pam Libxslt-devel Tcl-deve L PYTHON-DEVEL-Y

All packages are automatically downloaded and installed (remember to configure a public address for the virtual machine).

Wait until all the software installation is complete, we can download the latest PostgreSQL installation package, in the browser input https://ftp.postgresql.org/pub/source/, you can see the latest version is released August 31, 2017 9.6.5

Execute the following command to download the latest PostgreSQL installation package to the specified directory

# wget Https://ftp.postgresql.org/pub/source/v9.6.5/postgresql-9.6.5.tar.bz2-P/root/

Go to the download directory and unzip the installation package with the TAR command

# tar JXVF postgresql-9.6.5.tar.bz2

This command extracts the installation package to postgresql-9.6.5, then we enter the directory to perform the installation deployment:

# CD postgresql-9.6.5

Specifies that PostgreSQL is installed in the OPT directory

#./configure--prefix=/opt/postgresql-9.6.5

If we need to generate all the content that can be generated, including documents (HTML and man pages) and other modules (contrib), then execute the following command to complete the installation:

# Gmake Install-world

Now that the PostgreSQL package is installed, we need to initialize and configure the database:

First, we can create a link to the installation directory, just to simplify the later command, but also to skip this step, but the subsequent command to make the corresponding changes:

# ln-s/opt/postgresql-9.6.5/opt/pgsql

The installed PostgreSQL does not create a database, so we will first create the database directory

# mkdir-p/opt/pgsql_data

But it's dangerous to run the database directly under root, so we're going to create a new user and modify the user's profile.

# Useradd Postgres

# Chown-r Postgres.postgres/opt/pgsql_data

# Su–postgres

Edit the Bash_profile file. These rows are added to the end of the Bash_profile file,

Cat >> ~/.bash_profile <<eof

Export pgport=1999

Export Pgdata=/opt/pgsql_data

Export Lang=en_us.utf8

Export Pghome=/opt/pgsql

Export path=\ $PATH: \ $PGHOME/bin

Export manpath=\ $MANPATH: \ $PGHOME/share/man

Export data= ' date + '%y%m%d%h%m '

Export Pguser=postgres

Alias rm= ' Rm-i '

Alias Ll= ' Ls-lh '

Eof

Execute the Bash_profile file, or exit the current account and re-enter to make sure the parameters are in effect.

$ source. bash_profile

Here, the database configuration is complete, we can use the following two commands to view the installation directory, the parameters are correct

$ which psql

$ psql-v

When we see the above feedback, the installation is basically no problem.

Next we initialize the database:

$ initdb-d $PGDATA-e UTF8--locale=c-u postgres-w

You also need to configure the database startup file:

# cd/root/postgresql-9.6.5/contrib/start-scripts

# CP Linux/etc/init.d/postgresql

Modify the two variables in the/etc/init.d/postgresql file. Installation path set to PostgreSQL:/opt/pgsql. PGDATA data storage path set to PostgreSQL:/opt/pgsql_data

# sed-i ' 32s#usr/local#opt# '/etc/init.d/postgresql

# sed-i ' 35s#usr/local/pgsql/data#opt/pgsql_data# '/etc/init.d/postgresql

To set the startup file as an executable file

# chmod +x/etc/init.d/postgresql

Now we can start PostgreSQL.

#/etc/init.d/postgresql Start

To check that the PostgreSQL endpoint is turned on, be aware that PostgreSQL only allows the native connection to the database at this time because we have not configured the listening port and the trust endpoint:

# Netstat-tunlp|grep 1999

The installation deployment is complete, so let's connect to the database and set up a simple database to verify the following:

Switch to Postgres user

# Su–postgres

Create a database

$ createdb Events

Link a newly created database

$ psql-d Events

Create our first table

CREATE TABLE Potluck (name VARCHAR), food VARCHAR (+), confirmed CHAR (1), signup_date date);

You have now set up a 4 list with the following column names and restrictions:

The "Name" column is limited by the VARCHAR command and must be less than 20 characters.

The "Food" column indicates which foods each person will carry. VARCHAR restricts this text to 30 characters or less.

The "confirmed" column records whether the person accepted the dinner invitation. Acceptable values are "Y" and "N".

The "date" column will be displayed when they sign up for the event. Postgres requires a date format of YYYY-MM-DD.

View the Created table

\dt

Insert a row of data

INSERT into potluck (name, food, confirmed, signup_date) VALUES (' John ', ' Noodle ', ' Y ', ' 2017-09-21 ');

Add a few more

INSERT into potluck (name, food, confirmed, signup_date) VALUES (' Sandy ', ' rice ', ' N ', ' 2017-09-21 ');

INSERT into potluck (name, food, confirmed, signup_date) VALUES (' Tom ', ' BBQ ', ' Y ', ' 2017-09-20 ');

INSERT into potluck (name, food, confirmed, signup_date) VALUES (' Tina ', ' salad ', ' Y ', ' 2017-09-19 ');

Querying in a table

SELECT * from Potluck;

Update data

UPDATE Potluck Set confirmed = ' Y ' WHERE name = ' Sandy ';

UPDATE Potluck Set food = ' Rice ' WHERE name = ' Sandy ';

Delete data

Delete from potluck where name = ' John ';

In the next article, we'll look at how to modify PostgreSQL database parameters to optimize performance.

PostgreSQL on azure.cn: Installation deployment