Shulou(Shulou.com)06/01 Report--

◆◆

◆ PostgreSQL installation and deployment ◆

◆◆

PostgreSQL is used in the project. At present, it only involves installation, deployment and basic use. After a few days of study, I have a preliminary understanding of PG and do this arrangement.

Environment: PostgreSQL 9.6+Redhat 6.3

First, install the database

1. Delete the old version of PostgreSQL in the system

(1) View the existing version:

Rpm-qa | grep postgres

(2) check the pg service and stop the service

View Service: service postgresql-9.4 status

Stop: service postgresql-9.4 stop

View the process: ps-ef | grep postgres

Stop: kill-9 12345

(3) Delete the existing version

Rpm-nodeps-e postgresql94-libs-9.4.10-1PGDG.rhel6.x86_64

Rpm-nodeps-e postgresql94-server-9.4.10-1PGDG.rhel6.x86_64

Rpm-nodeps-e postgresql94-contrib-9.4.10-1PGDG.rhel6.x86_64

Rpm-- nodeps-e postgresql94-9.4.10-1PGDG.rhel6.x86_64

2. Download the new version

Https://yum.postgresql.org/9.6/redhat/ this address is the download link for version 9.6 of the redhat platform. If you need to download another version, you can change it to another version. For example, 9.3: https://yum.postgresql.org/9.3 and then select the platform

(1) for basic installation, you need to download four media:

Postgresql96-libs-9.6.2-2PGDG.rhel6.x86_64.rpm

Postgresql96-server-9.6.2-2PGDG.rhel6.x86_64.rpm

Postgresql96-9.6.2-2PGDG.rhel6.x86_64.rpm

(2) extended installation, two media:

Postgresql96-contrib-9.6.2-2PGDG.rhel6.x86_64.rpm

Postgresql96-devel-9.6.2-2PGDG.rhel6.x86_64.rpm

3. Install PostgreSQL

(1) the basic package is installed in the following order:

Rpm-ivh postgresql96-libs-9.6.2-2PGDG.rhel6.x86_64.rpm

Rpm-ivh postgresql96-9.6.2-2PGDG.rhel6.x86_64.rpm

Rpm-ivh postgresql96-server-9.6.2-2PGDG.rhel6.x86_64.rpm

(2) expansion:

Rpm-ivh postgresql96-contrib-9.6.2-2PGDG.rhel6.x86_64.rpm

Rpm-ivh postgresql96-devel-9.6.2-2PGDG.rhel6.x86_64.rpm

Note: the installation of server may lack the system dependency package openssl, or need to be upgraded. For example, an error was reported when installing server this time: libcrypto.so.10 (OPENSSL_1.0.1_EC) (64bit) is needed by postgresql96-server-9.6.2-2PGDG.rhel6.x86_64.

Delete the old version of openssl:

Rpm-qa opensssl

Rpm-nodeps-e openssl

After you download the openssl-1.0.1e-48.el6.x86_64.rpm installation, you can continue to install server.

Download and install according to the specific version of the prompt.

4. Assign the initialization data to the custom path

PostgreSQL initializes configuration files such as data files to / var/lib/pgsql/9.6/data by default. In practical applications, we want to place them under a custom path (a separately mounted file system). This requires specifying the path to the custom path before initializing the data.

Modify the $PGDATA variable in the postgres user .bash _ profile to the custom path.

Note: this custom path must have postgres:postgres permission, pay attention to the authorization.

5. Initialize the database (the main Postgres library will be built during the initialization process. If there is no master library, PostgreSQL cannot start)

Service postgresql-9.6 initdb

6. Start the database

Service postgresql-9.6 start

Second, use the database

0. Introduce the structure of postgreSQL database in order to have a clear management idea.

(1) postgreSQL object hierarchy: server, database, schema, table, or other type of object. The user is clearly separated from schema. Creating a user has nothing to do with creating a schema. After creating the user, give it the permission to specify the database / schema, and you can access the corresponding db and shema.

The schma is created under a certain db. After creation, you can create tables and other objects under the schema, and users can only take corresponding actions on them if they have the corresponding permission.

After installing postgresql, start the PostgreSQL service and enter the default postgres user.

1. Log in to the database using the postgres user (superuser, sysdba similar to oracle):

# su-postgres # the postgres system user is the default postgreSQL user, similar to the oracle oracle user.

$psql # after entering postgres users, you can log in to pg by executing psql. By default, log in to the postgres user postgres library; sqlplus / as sysdba similar to oracle

Note: after installation, the psql executable program is in / usr/pgsql-9.6/bin/psql. If psql cannot be executed under the postgres user, configure as follows:

(1) check the environment variables corresponding to the psql of postgres users:

Whereis psql

Return: psql: / usr/sbin/psql / usr/share/man/man1/psql.1

(2) soft link the path to the actual path / usr/pgsql-9.6/bin/psql of the newly installed psql

Ln-s / usr/pgsql-9.6/bin/psql / usr/sbin/psql

2. Create a db,user, assign the db to the specified user management, and give the right

CREATE USER postgr WITH PASSWORD 'postgr'

CREATE DATABASE dsgdb OWNER postgr

GRANT ALL PRIVILEGES ON DATABASE dsgdb to postgr

3. Basic operation

The operation of database structure, table structure, etc., can log in to the database and use\? Command to list. The most basic commonly used commands are listed below:

\ l # list which databases, owners, character sets, permissions, etc. are available in the current PostgreSQL

\ dn # lists all schema in the current library

\ d # list the tables, views, and sequences in the current database

\ d tablename # View the table structure of the specified table

Select current_database (); # View the database currently logged in

Select * from current_user; # to view currently logged in users

\ c dbname username # switch between logged in databases and users

3. Log in to the specified library with the specified user:

# su-postgres

$psql-d dsgdb-U postgr # psql followed by-d parameter specifies db,-U parameter to specify login user. Other parameters can be viewed by psql-- help

Set search_path to dsg_test; # sets the mode search path to a schema; and defaults to public

Set search_path to public; # mode search path is switched back to the default public

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.