PostgreSQL database (Getting Started)

There are a variety of Installation manuals for each platform to be installed. You can search for details in google. Here is the Installation Method On debian, ubuntu class: aptitudeinstallpostgresql # This is the database aptitudeinstallpgadmin3 # This is the management tool on the GUI. Here the debian machine (172.16.70.175) is my notebook and I need it

There are a variety of Installation manuals for each platform to be installed. You can search for details in google. Here is the debian installation method, ubuntu class: aptitude install postgresql # This is the database aptitude install pgadmin3 # This is the management tool for the GUI. Here the debian machine (172.16.70.175) is my notebook, yes

Install

Various platforms have a wide range of Installation manuals, which can be searched in google for details. Here is the debian installation method, ubuntu is similar:

Aptitude install postgresql # This is the database aptitude install pgadmin3 # This is a management tool on the GUI

Here, debian machine (172.16.70.175) is my notebook. I need a pgadmin3 and then install postgresql on the experimental machine (fedora system, 172.16.70.254.

# rpm -qa|grep postgresqlpostgresql-libs-8.3.7-1.fc10.i386postgresql-8.3.7-1.fc10.i386postgresql-server-8.3.7-1.fc10.i386

Initialize Configuration

Postgresql users and system users are separated. We need to initialize the database, set the Database Loading user, and configure the postgresql access permission.

Set Password

If deb or rpm packages are installed on debian/ubuntu or fedora systems, postgres users will be created. Run the psql command with this user permission to set the postgres user password:

# Su ipvs-c psql template1 welcome to psql 8.3.7, PostgreSQL interactive text terminal. Type \ copyright to display the release terms \ h to display the description of SQL commands \? Display the pgsql command description \ g or end with a semicolon (;) and run the query \ q to exit S = # alter user Login s with password 'abc123'; ALTER ROLEpostgres = # \ q

The above abc123 is the password we set.

Create a database

su postgres -c createdb mydb

Configure remote connection

1. First, check whether the firewall is enabled. If enabled, You need to allow the connection of port 5432. Disable the firewall if you do not know how to configure it.

2. Configure the postgresql. conf file, which is in the data directory under the pgsql directory of the database. My current position on fedora is/var/lib/pgsql/data/postgresql. conf.

Listen_addresses = '*' # Remove the comments of this sentence. '*' indicates listening to links of all network addresses.

3. Configure the pg_cmd.conf file, which is usually in the same directory as the postgresql. conf file:

Host all 172.16.70.175 255.255.252.0 md5 # I added this sentence at the end # My notebook IP address is 175, and netmask is 255.255.252.0 # the machines in the same network segment as me are linked here

4. Restart the postgresql service.

#/Etc/init. d/postgresql restart stop postgresql service: [OK] Start postgresql service: [OK]

User ManagementCreate user createuser

Createuser is the encapsulation of the SQL command CREATE USER. Command: createuser [-a] [-A] [-d] [-D] [-e] [-P] [-h host name] [-p port] user name parameter description: [-a]: allows other users to be created. [-d]: allows A user to create a data user, which is equivalent to creating A Super User. [-a]: this user is not allowed to create a database; [-e]: displays the execution process to Shell; [-P]: sets the password when creating a user; [-D]: this user is not allowed to create a data code. [-h host name]: Creates a user for ipvs on a host; [-p port]: used together with the-h parameter, specifies the host port.

1. Run the createruser command directly.

# CreateuserEnter name of role to add: jianleeShall the new role be a superuser? (Y/n) nShall the new role be allowed to create databases? (Y/n) yShall the new role be allowed to create more new roles? (Y/n) ycreateuser: Unable to connect to database postgres: Fatal error: User "root" Ident authentication failed

The reason for the above command running error is that the root user does not have the permission to access the database. We use the postgres user with the previous password to run the command:

# Su postgresbash-3.2 $ createuserEnter name of role to add: jianleeShall the new role be a superuser? (Y/n) n # If y is selected, then jianlee is the Super User Shall the new role be allowed to create databases? (Y/n) y # If y is not selected above, here the Shall the new role be allowed to create more new roles? (Y/n) y # Same as above

2. Create a user (Remote User Creation) for the specified host and port)

# Su postgrespostgres @ jianlee:/root $ createuser-h 172.16.70.254-p 5432-D-A-e turbolinuxShall the new role be allowed to create more new roles? (Y/n) y password: # enter the user's S password create role turbolinux nosuperuser nocreatedb createrole inherit login;

In this example, I first run the createuser command under the ipvs user on the debian machine.-h and-p specify the ip address and port of the remote host. turbolinux is the username I want to create. You must start the postgresql server remotely (172.16.70.254) and the ipvs user can access debian (172.16.70.175.

3. Create a Super User

$ Createuser-P-d-a-e turbo_rdEnter password for new role: enter again: create role turbo_rd PASSWORD 'authorization' \ superuser createdb createrole inherit login;

The parameters are described in the createuser parameter description at the beginning of this section.

Delete user dropuser

$ Dropuser -- helpdropuser removes a PostgreSQL role. usage: dropuser [OPTION]... [ROLENAME] Option:-e, -- echo: displays the command-I sent to the server, -- prompt-h before interactive deletes anything, -- host = host Nam the host name or socket directory of the machine on which the database server is located-p, -- port = PORT database server port number-U, -- username = USERNAME connected to the user (not the user name to be deleted) -W, -- password force password prompt -- help displays this help information, then exits -- version to output version information, and then exits the bug report
 
  
.
 

1. Local Deletion

$ Dropuser-I-e turbo_rdRole "turbo_rd" will be permanently removed. Are you sure you want? (Y/n) yDROP ROLE turbo_rd;

2. Remote Deletion

$ Dropuser-p 5432-h 172.16.70.254-I-e turbolinuxRole "turbolinux" will be permanently removed. Are you sure you want? (Y/n) y password: drop role turbolinux;

Reminder again: the operator of this command must be a user who can log on to the database on 254 machines. The 'Password' is also the user's database logon password.

Simple database management create database createdb

Createdb Chinese

Note: The user who runs this command must be a postgresql Server user and have the permission to create a database.

Delete database dropdb Access Database

1. Use psql command line

$ Psql welcome to psql 8.3.7, PostgreSQL interactive text terminal. Type \ copyright to display the release terms \ h to display the description of SQL commands \? Display the pgsql command description \ g or end with a semicolon (;) to execute the query \ q to exit Chinese = #

2. Use pgadmin3 graphical interface management tool

Table operations

After creating the database, we can create tables in the database. My postgresql version is 8.3 (debian lenny), the table name, the names and comments of all fields in the table can be UTF-8 characters. However, the English name can be supplemented by the TAB key, but here I still use Chinese. For example, refer to postgresql wiki.

Create a new table

Start the psql command line program and create a new table:

Root @ jianlee :~ # Psql-U postgres-h localhost welcome to psql 8.3.6, which is a PostgreSQL interactive text terminal .... Postgres = # create table weather (city varchar (80), -- this is the city name minimum temperature int, -- the lowest temperature in a day, the highest temperature int, -- the highest temperature precipitation in a day real, -- date of the day's precipitation -- date); create table <= The created successfully S = #

Note:: The last line is not followed by the date character','No! If a comma is added, a syntax error occurs.

Besides common\ Q \?And other commands. One command in psql is';'End. Blank spaces (spaces, tabs, and line breaks) can be used in SQL ).-Is the start of the annotation and ends with the linefeed. SQL is not case sensitive. It is used in double quotation marks to distinguish between uppercase and lowercase characters.

Common error pgadmin3-"server tool not installed"

Reference: http://techxplorer.com/2008/01/16/installing-postgresql-server-instrumentation-under-ubuntu/

1. Install the postgresql-contrib package

2. Run the following command:

psql -U postgres -d postgres -h localhost < \/usr/share/postgresql/8.3/contrib/adminpack.sql