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

Centos series installation is divided into:

yum installation

source installation

I. Yum installation

Follow the official installation documentation.

Install postgresql official yum repository

yum install https://download.postgresql.org/pub/repos/yum/9.6/redhat/rhel-7-x86_64/pgdg-centos96-9.6-3.noarch.rpm

Install postgresql database

yum install postgresql96-server postgresql96-contrib

Initialize and start the database

service postgresql initdb

service postgresql start

test data

su - postgres

psql -l

Second, source code installation

./ configurationmakesumake installadduser postgresmkdir /usr/local/pgsql/datacown postgres /usr/local/pgsql/datasu - postgres/usr/local/pgsql/bin/initdb -D /usr/local/pgsql/data #Initialize database/usr/local/pgsql/bin/postgres -D /usr/local/pgsql/data >logfile 2>&1 & The requested URL/usr/local/pgsql/bin/createdb/was not found on this server. The requested URL/usr/local/pgsql/bin/psql/was not found on this server. #Check the test database

Set postgresql directory to system environment variable

Add user postgres

Copy the system service startup script to the system directory

cp /path/postgresql/contrib/start-scripts/linux /etc/init.d/postgresql

III. Configuration of postgresql

Configure remote access.

Modify pg_hba.conf file to configure new IP address and netmask to access this database.

Modify the listen_addresses="localhost" configuration item in the postgresql.conf file, replace localhost with *, and listen to all IP addresses.

Then restart the postgresql service.

4. Basic operation of postgresql:

After installation, a database named postgres and a database user named postgres are generated by default. Note here that a Linux system user named postgres is also generated.

1. Use PostgreSQL console.

Switch to postgres user.

sudo su - postgres

Log in to the PostgreSQL console using the psql command.

This is equivalent to system user postgres logging into the database as a database user with the same name, without entering a password. If everything is OK, the system prompt changes to "postgres=#," indicating that you have entered the database console. The following commands are done in the console.

(1) Use the\password command to set a password for the postgres user.

# \password postgres

(2) Create database user duser (just created is Linux system user) and set password.

CREATE USER dbuser WITH PASSWORD 'password';

The third thing is to create a user database, here exampledb, and specify the owner as dbuser.

CREATE DATABASE exampledb OWNER dbuser;

The fourth thing is to give all permissions to the exampledb database to dbuser, otherwise dbuser can only log on to the console and has no database operation permissions.

GRANT ALL PRIVILEGES ON DATABASE exampledb to dbuser;

Finally, exit the console using the\q command (or press ctrl+D).

\q

2. Use the shell command line.

Adding new users and databases can be done from the shell command line, in addition to within the PostgreSQL console. This is because PostgreSQL provides command-line programs createuser and createdb. Again, take the example of creating a new user dbuser and database exampledb.

First, create the database user duser and designate it as superuser.

sudo -u postgres createuser --superuser dbuser

Then, log in to the database console, set the password for the dbuser, and exit the console when you're done.

sudo -u postgres psql

\password dbuser

\q

Next, at the shell command line, create the database exampledb and specify the owner as dbuser.

sudo -u postgres createdb -O dbuser exampledb

sign-on database

After adding a new user and a new database, log in to the database as the new user, using the psql command.

psql -U dbuser -d exampledb -h 127.0.0.1 -p 5432

The parameters of the above command mean the following: -U specifies the user, -d specifies the database, -h specifies the server, and-p specifies the port.

After entering the above command, you will be prompted for the password of the dbuser user. If you type correctly, you can log in to the console.

The psql command exists in shorthand form. If the current Linux system user is also a PostgreSQL user, the username (part of the-U argument) can be omitted. For example, my Linux system user name is ruanyf, and the PostgreSQL database has a user with the same name, then after I log in to the Linux system as ruanyf, I can directly log in to the database using the following command, and no password is required.

psql exampledb

At this point, if there is a database with the same name as the current system user in PostgreSQL, even the database name can be omitted. For example, suppose there is a database called ruanyf, you can log in to it by typing psql.

psql

Alternatively, if you want to recover external data, you can use the following command.

psql exampledb

< exampledb.sql 控制台命令 除了前面已经用到的\password命令（设置密码）和\q命令（退出）以外，控制台还提供一系列其他命令。 \h：查看SQL命令的解释，比如\h select。 \?：查看psql命令列表。 \l：列出所有数据库。 \c [database_name]：连接其他数据库。 \d：列出当前数据库的所有表格。 \d [table_name]：列出某一张表格的结构。 \du：列出所有用户。 \e：打开文本编辑器。 \conninfo：列出当前数据库和连接的信息。 postgres数据库的操作 查看版本： psql --version 或 SELECT version(); 查看所有数据库（包括详细参数）：select * from pg_database; 创建数据库 CREATE DATABASE test WITH OWNER = postgres ENCODING = 'UTF8'; 选择数据库： \c databasename 导出PostgreSQL数据库中的数据： pg_dump -U postgres -f mydatabase.sql mydatabase 备份数据库： pg_dump -h localhost -U postgres databasename >

/tmp/databasename.bak.yyyymmdd.sql

Recovery database (sql file is pg_dump exported file on the line, can be the entire database, can be just a single table, can also be just a structure, etc.):

psql -h localhost -U postgres -d databasename

< /tmp/databasename.bak.yyyymmdd.sql 导出数据结构，主要是加上参数-s： pg_dump -U username -W dbname -f /tmp/filename.sql 查看数据库大小：select pg_size_pretty(pg_database_size('test')); 导入数据时首先创建数据库再用psql导入： $ createdb newdatabase $ psql -d newdatabase -U postgres -f mydatabase.sql postgresql数据表的操作 查看所有表：\dt 查看某个表的结构：\d tablename 查看表的索引： select * from pg_indexes where tablename='log'; 导出某个表： pg_dump -h localhost -U postgres -t tablename dbname >

test.sql

Export the structure of a table, also add parameter "-s":

pg_dump -h localhost -U postgres -t tablename -s dbname > test_construct.sql

Export the data of a table, add parameter "-a":

pg_dump -h localhost -U postgres -t tablename -a dbname > test_data.sql

View sequences: select * from information_schema.sequences where sequence_schema = 'public';

Select pg_size_pretty(pg_relation_size ('test '));

Postgresql database user actions

Postgres user is an account with the highest local administrator privileges automatically created by the database system. You can directly use psql command to enter the local database system without password.

Change postgres user password:

ALTER USER postgres WITH PASSWORD 'postgres';

create user

CREATE USER username WITH PASSWORD 'password';

CREATE DATABASE dbname;

GRANT ALL PRIVILEGES ON DATABASE xdbname to username;

Create a database belonging to a user

createdb --encoding=UTF8 --owner=username --template=template_postgis -U username

--encoding=UTF8 Sets the character set of the database

--owner=foo Sets the owner of the database

--tmplate=template_postgis Sets the template for building the library, which supports spatial data operations

--U foo creates database with foo user identity

Create a user who belongs to a role

create user username with password 'password' in role postgres;

Assign the user access to a database

grant all privileges on database databasename to username;

Using help is the best help.

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.