linux直接执行以下命令:
./configure --prefix=/work/postgresql-9.1.9/binpost
当进行交叉编译的时如下:我的编译是有些问题直接跳过了下面俩个包的检测
./configure --host=arm-none-linux-gnueabi --prefix=/work/postgresql-9.1.9/binpost --without-readline --without-zlib
roupadd postgresql
useradd -g postgresql postgresql
创建数据库目录和日志目录
mkdir /usr/local/pgsql/data
mkdir -p /usr/local/pgsql/log
touch /usr/local/pgsql/log/pgsql.log
改变属主:
chown -R postgresql:postgresql /usr/local/pgsql/data
chown -R postgresql:postgresql /usr/local/pgsql/log
chown -R postgresql:postgresql /usr/local/pgsql/log/pgsql.log
3:初始化及创建数据库
su - postgresql
initdb -D /usr/local/pgsql/data
启动数据库:pg_ctl -D /usr/local/pgsql/data -l /usr/local/pgsql/log/pgsql.log start
[root@localhost /work/postgresql-9.1.9]$pg_ctl -D /usr/local/pgsql/data -l /usr/local/pgsql/log/pgsql.log start
pg_ctl: cannot be run as root
Please log in (using,e.g.,"su") as the (unprivileged) user that will
own the server process.
[root@localhost /work/postgresql-9.1.9]$
上面是因为用户的原因,故需要切换到之前创建的用户postgresql。
当切换用户之后:启动数据库。
postgresql@localhost ~]$ pg_ctl -D /usr/local/pgsql/data -l /usr/local/pgsql/log/pgsql.log start
server starting
4:创建database及tables
创建数据库用户
createuser -sADEP psmpAdmin
访问数据库: psql -d psmp -U psmpAdmin
[postgresql@localhost ~]$ psql -d psmp -U psmpAdmin
psql (8.4.1)
Type "help" for help.
psmp=> \d
List of relations
Schema | Name | Type | Owner
--------+---------+-------+-----------
public | company | table | psmpAdmin
(1 row)
psmp=> \l
List of databases
Name | Owner | Encoding | Collation | Ctype | Access privileges
-----------+------------+----------+-------------+-------------+---------------------------
postgres | postgresql | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
psmp | postgresql | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
template0 | postgresql | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgresql
: postgresql=CTc/postgresql
template1 | postgresql | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgresql
: postgresql=CTc/postgresql
(4 rows)
psmp=> \c psmp //链接psmp数据库
psql (8.4.1)
You are now connected to database "psmp".
psmp=> select * from COMPANY;
id | name | age | address | salary | join_date
----+-------+-----+----------------------------------------------------+--------+------------
3 | Teddy | 23 | Norway | 20000 |
1 | Paul | 32 | California | 20000 | 2001-07-13
(2 rows)
psmp=>
=====================================================================================================
CREATE TABLE COMPANY(
ID INT PRIMARY KEY NOT NULL,
NAME TEXT NOT NULL,
AGE INT NOT NULL,
ADDRESS CHAR(50),
SALARY REAL,
JOIN_DATE DATE
);
drop table company;
INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY,JOIN_DATE) VALUES (1,'Paul',32,'California',20000.00,'2001-07-13');
INSERT INTO COMPANY (ID,JOIN_DATE) VALUES (3,'Teddy',23,'Norway',DEFAULT );
SELECT * FROM COMPANY;
5:CGI代码访问数据库
#include "libpq-fe.h" #include <stdio.h> #include <stdlib.h> static void exit_nicely(PGconn *conn) { PQfinish(conn); exit(1); } int main() { int i,nFields,j; struct pg_conn * conn = 0; PGresult *res = 0; printf("%s\r\n\r\n","Content-Type:text/html"); conn = PQsetdbLogin("localhost","5432","","psmp","psmpAdmin","suiyuan");//succedd if(PQstatus(conn) != CONNECTION_OK) { printf("connect fail \n"); } else { printf("connect success\n"); } res = PQexec(conn,"BEGIN"); if(PQresultStatus(res) != PGRES_COMMAND_OK) { printf("execute sql fail %s",PQerrorMessage(conn)); PQclear(res); exit_nicely(conn); } PQclear(res); res = PQexec(conn,"DECLARE myportal CURSOR FOR select * from company"); if (PQresultStatus(res) != PGRES_COMMAND_OK) { printf("DECLARE CURSOR Failed: %s",PQerrorMessage(conn)); PQclear(res); exit_nicely(conn); } PQclear(res); res = PQexec(conn,"FETCH ALL in myportal"); if (PQresultStatus(res) != PGRES_TUPLES_OK) { printf("FETCH ALL Failed: %s",PQerrorMessage(conn)); PQclear(res); exit_nicely(conn); } nFields = PQnfields(res); printf("<table><font color=\"CC0000\" size=+2>%s</font>"); printf("<tr>"); for (i = 0; i < nFields; i++) { //printf("%-15s",PQfname(res,i)); printf("<td>%s</td>",i)); } printf("</tr>"); printf("\n\n"); for (i = 0; i < PQntuples(res); i++) { printf("<tr>"); for (j = 0; j < nFields; j++) { // printf("%-15s",PQgetvalue(res,i,j)); printf("<td>%s</td>",j)); } printf("</tr>"); } printf("</table>"); PQclear(res); /* res = PQexec(conn,"delete from test where name = 'dog'"); PQclear(res); */ res = PQexec(conn,"CLOSE myportal"); PQclear(res); res = PQexec(conn,"END"); PQclear(res); PQfinish(conn); return 0; }
运行CGI程序如下:
在运行CGI的时候,需要首先 httpd 运行。CGI程序放置的位置如下: