学习需要安装一个最新的postgresql版本，参考最新的链接

COMP9315 24T1 - Prac Exercise 01 (unsw.edu.au)

cgi.cse.unsw.edu.au/~cs9315/24T1/pracs/p01/beer.dump

PostgreSQL: File Browser

内容待一步一步跟着链接文档操作：

Prac Exercise 01
Setting up your PostgreSQL Server
Aims 
This exercise aims to get you to: 
• set up your directories on a file system 
• install a PostgreSQL database server  
You ought to get it done by the middle of Week 2.
Notation: In the examples below, 
we have used the $ sign to represent the prompt from the Unix/Linux shell. 
The actual prompt may look quite different on your computer (e.g. it may contain the computer's hostname, or your username, or the current directory name). 
In the example interactions, all of the things that the computer displays are in this font. 
The commands that you are supposed to type are in this bold font. 
Comments in the examples are introduced by '...' and are written in this grey font; 
comments do not appear on the computer screen, they simply aim to explain what is happening. 
Whenever we use the word edit, this means that you should use your favourite text editor (e.g. vi, emacs, gedit, etc.) 
Finally, some commands use YOU as a placeholder for your CSE username (which is usually your zid). 
This is the same as the $USER environment variable in Linux.

Background
PostgreSQL has three major components:
• the source code (and the compiled *.o files) (approx 200MB)
• the installed executables (like pg_ctl and psql) (approx 20MB)
• the data (including configuration files and databases) (at least 35MB)
You will not be able to fit the above components under your CSE home directory (insufficient disk quota)
The practical work for the assignments can be carried out on a special CSE server called vxdb. 
You run your own PostgreSQL server on this machine and are effectively the database administrator of this server. 
This machine has been configured to run large numbers** of PostgreSQL servers.
** Note: "large numbers" is around 300. If you leave your work to the last minute, 
and find 400 other students all trying to run PostgreSQL on vxdb, performance will be sub-optimal次优. 
Of course, you can avoid this potential bottleneck by installing and running PostgreSQL on your home machine.
自己搞台机器运行pg

You must put your PostgreSQL source code, installed executables and data under the /localstorage/$USER directory on vxdb. 
You must run the server process on vxdb; do not run your PostgreSQL server process on any other CSE machines; 
if you do, your PostgreSQL server will most likely be terminated automatically not long after it starts.

If you're doing all of this work on a laptop or desktop at home, then you can configure things however you like. 
You will still need folders for the same three components (source code, executables, and data), but you can place them wherever you like. 
PostgreSQL doesn't require any special privileges to run (at least on Unix-based systems like Linux and Mac OS X), 
so you do not need to create a special privileged PostgreSQL user; you can run the server as yourself.
没有特权要求

Reminder提醒: You should always test your work on vxdb before you submit assignments, 
since that's where we'll be running our tests to award your marks.

Getting started on vxdb--登录vxdb服务器
You may have a /localstorage/$USER directory on vxdb already (e.g. from a previous database course). 
If so, you can skip this section; but you might want to clean out any pgsql directory before you continue.
You can log into vxdb from a command-line (shell) window on any CSE machine (including vlab) via the command
$ ssh nw-syd-vxdb.cse.unsw.edu.au
If you're doing this exercise from home, you can use any ssh client, but you'll need to refer to nw-syd-vxdb.cse as d.cse:
$ ssh YourZID@d.cse.unsw.edu.au
You can check whether you're actually logged in to vxdb by using the command:
$ hostname
nw-syd-vxdb
Your home directory at CSE is directly accessible from vxdb.
The first time you log in to vxdb, it automatically creates a directory under /localstorage to hold your databases:
$ ls -al /localstorage/$USER
This directory is initially empty, but we're about to put the files for a PostgreSQL server into it.

Setting up your PostgreSQL Server
Reminder: If you are working from CSE make sure you are ssh'd on into vxdb. 
The times below are approximate大概; they could double or triple 三重 depending on your environment.

Quick summary (for experts only):专家级
Non-experts should go straight to the detailed instructions below.

$ cd /localstorage/$USER
$ tar xfj /web/cs9315/24T1/postgresql/postgresql-15.6.tar.bz2
$ cd postgresql-15.6
$ ./configure --prefix=/localstorage/$USER/pgsql
$ make
$ make install
$ cp  /web/cs9315/24T1/postgresql/env  /localstorage/$USER/env
$ source /localstorage/$USER/env
$ which initdb
$ initdb
$ ls $PGDATA
$ edit $PGDATA/postgresql.conf
$ which pg_ctl
$ pg_ctl start -l $PGDATA/log
$ psql -l
$ pg_ctl stop

Installation Details (for non-experts):非专家，给出详细步骤
步骤1：Setting up directories 设置目录
The first step is to make sure that the directory /localstorage/$USER exists. You can check this via the command:
$ ls -l /localstorage/$USER
If the above command says something like "No such file or directory", then you should create it using the instructions above.
Once you have a directory on the /localstorage filesystem, the next step is to place a copy of the PostgreSQL source code under this directory. The following commands will do this:
$ cd /localstorage/$USER
$ tar xfj /web/cs9315/24T1/postgresql/postgresql-15.6.tar.bz2
This creates a subdirectory called postgresql-15.6 under your /localstorage/$USER directory and unpacks all of the source code there. 
This produces no output and will take a few moments to complete. 
If you want to watch as tar unpacks the files, use xvfj instead of xfj as the first argument to tar.

步骤2：Initial compilation 初始化编译
Once you've unpacked the source code, you should change into the newly created postgresql-15.6 directory and configure the system so that it uses the directory /localstorage/$USER/pgsql to hold the executables for your PostgreSQL server. (Note that /localstorage/$USER/pgsql does not exist yet; it will be created in the make install step). 
The following commands will do the source code configuration:
$ cd /localstorage/$USER/postgresql-15.6
$ ./configure --prefix=/localstorage/$USER/pgsql
The configure command will print lots of messages about checking for various libraries/modules/etc. 
This process will take a minute, and should produce no errors.

configure: error: readline library not found

sudo apt install libreadline-dev
yum -y install -y readline-devel

yum install -y readline-devel
 

After configuring the source code, the next step is to build all of the programs. 
Stay in the postgresql-15.6 directory and then run the command:
$ make
This compiles all of the PostgreSQL source code, and takes around 4-5 minutes (depending on the load on vxdb). 
It will produce lots of output, but should compile everything OK. If anything goes wrong, the make process will stop partway through with an obvious error message.

步骤3：Installing executables 安装
Once the PostgreSQL programs are compiled, you need to install them. The following command does this:
$ make install 
This creates the directory /localstorage/$USER/pgsql, and copies all of the executables (such as pg_ctl and psql) under that directory. 
It will take a minute to do this, and will produce quite a bit of output while it's doing it.

步骤4:Data directories 数据目录
You're not finished yet, however, since PostgreSQL has no directory in which to store all of its data. 
You will install the data directories under /localstorage/$USER/pgsqlBefore doing anything with the database, however, you need to ensure that your Unix environment is set up correctly. 
We have written a small script called env that will do this. In this set up stage, you should copy this script to your /localstorage directory:
$ cp  /web/cs9315/24T1/postgresql/env  /localstorage/$USER/env
The env script contains the following:

export PGDATA=/localstorage/$USER/pgsql/data
export PGHOST=$PGDATA
export LD_LIBRARY_PATH=/localstorage/$USER/pgsql/libexport PATH=/localstorage/$USER/pgsql/bin:$PATHalias p0="pg_ctl stop"
alias p1="pg_ctl -l $PGDATA/log start"

This script sets up a number of environment variables. The critical ones are:
• PGDATA which tells the PostgreSQL server where it's data directories are located
• PGHOST which tells PostgreSQL clients where are the socket files to connect to the server
Note that in the discussion below, we will use the string YOUR_PGDATA to refer to that value that you assigned to PGDATA in your env file and which has been set by source'ing the env file in your shell.

步骤5：Initialising data directories and running server 初始化数据并运行
Once you have a copy of the env script and have set the values appropriately, 
you need to invoke it in every shell window where you plan to interact with the database. 
You can do this by explicitly running the following command in each window:
$ source /localstorage/$USER/env
If that gets tedious, you might consider adding the above command to your shell's startup script (e.g., ~/.bash_profile).
Once you've set up the environment, check that it's ok via the following commands:
$ echo $PGDATA
YOUR_PGDATA ... i.e. whatever value you set it to ...
$ which initdb
/localstorage/YOU/pgsql/bin/initdb
$ which pg_ctl
/localstorage/YOU/pgsql/bin/pg_ctl
If the system gives you different path names to the above, then your environment is not yet set up properly. Are you sure that you source'd your env file?

If all of the above went as expected, you are now ready to create the data directories and run the server. You can do this via the command:
$ initdb
... some output eventually finishing with something like ...
Success. You can now start the database server using:
    pg_ctl -D YOUR_PGDATA -l logfile start
If you look at your data directory now, you should see something like:
$ ls $PGDATA
PG_VERSION    pg_hba.conf 

然后还要修改配置文件
You shouldn't start the server straight away, however, since there's one more bit of configuration needed. 
You need to edit the postgresql.conf file in the $PGDATA directory and change the values of the following:
4条修改：
• change the value of the listen_addresses parameter to '': this means that only Unix-domain sockets can be used to connect to the server (saving you fighting over TCP ports);
• reduce the value of max_connections from 100 to 10: this reduces the resources tied up by the server to support those connections potentially occurring; and 
• set the value of the unix_socket_directories parameter to the full path of your $PGDATA directory; make sure to input the literal path instead of using $USER (e.g. /localstorage/z5555555/pgsql/data): this specifies where PostgreSQL keeps its connection sockets, and should be the same as your $PGDATA so psql and other clients can connect; and
• set the value of max_wal_senders to e.g. 4 (or any value less than whatever value you use for max_connections) Once you're done, the "connections and authentications" part of your modified postgresql.conf file should look like (with the changes highlighted in red):

#------------------------------------------------------------------------------
# CONNECTIONS AND AUTHENTICATION
#------------------------------------------------------------------------------# - Connection Settings -listen_addresses = ''                   # what IP address(es) to listen on;# comma-separated list of addresses;# defaults to 'localhost'; use '*' for all# (change requires restart)
#port = 5432                            # (change requires restart)
max_connections = 10                    # (change requires restart)
#superuser_reserved_connections = 3     # (change requires restart)
unix_socket_directories = 'YOUR_PGDATA' # comma-separated list of directories# (change requires restart)
#unix_socket_group = ''                 # (change requires restart)
#unix_socket_permissions = 0777         # begin with 0 to use octal notation# (change requires restart)
....
max_wal_senders = 4

Note that it doesn't matter that the file says port = 5432: 
this value will be overridden by whatever you set your PGPORT environment variable to.
Note also that the 5432 also doesn't matter because the # at the start of the line means that it's a comment. In the case of the lines that you are supposed to change, make sure that you remove the # from the start of those lines.

Everything is now ready to start your PostgreSQL server, which you can do via the command: 万事俱备，可以启动了
$ pg_ctl start -l $PGDATA/log

You may have noticed that we provided you with shorthand to start and stop the server in the env file. The command p1 starts the server and p0 stops it.
简化命令

Note that PostgreSQL says "server starting", whereas it should probably say "attempting to start server".
A quick way to check whether the server is working is to run the command:
$ psql -l
It is possible that the server may not start correctly. If the server does not appear to have started, you can check why by looking at the tail of the server log:
$ tail -20 $PGDATA/log
... information about what happened at server start-time ...

错误定位
Note that you'll get error messages about not being able to run the statistics collector, 
and a warning that autovacuum was not started. These are not an issue at this stage.
If the server is running correctly, the psql -l will give you a list of databases like the above. 
If the server is not running, you'll get a message something like:
psql: could not connect to server: No such file or directory
    Is the server running locally and accepting
    connections on Unix domain socket "YOUR_PGDATA/.s.PGSQL.5432"?
If this happens, you should check the log file to find out what went wrong. (Other things to check in case of problems are described below).

Assuming that the server is running ok, you can now use it to create and manipulate databases (see the example below). 
Once you've finished your session using PostgreSQL, you need to stop the server.
$ pg_ctl stop
waiting for server to shut down.... done    

If you still have a process that's using the database (e.g. a psql process in another window), 
then the server won't be able to shut down. 
You'll need to quit all of the processes that are accessing the database before the above command will work.

步骤6：A Typical session with PostgreSQL

source /localstorage/$USER/env
p1
... hopefully concluding with the message ...
server started
psql -l
... hopefully giving a list of databases ...
createdb myNewDB
psql myNewDB
... do stuff with your database ... 
p0
... hopefully concluding with the message ...
server stopped

最终如果想清理：

Note that the data will persist across sessions. If you really want to clean out you whole server, you can use the command:

$ rm -fr /localstorage/$USER/pgsql

Reminder --最后提醒别忘了关机

You must shut down your server at the end of each session with PostgreSQL if you're working on the CSE workstations. Failure to do this means that the next student who uses that workstation may need to adjust their configuration (after first working out what the problem is) in order to start their server.

A Sample Database--一个简单数据库的例子

Once your server is up-and-running, you ought to load up the small sample database (on beers) and try a few queries on its data. This is especially important if you haven't used PostgreSQL before; you need to get used to its interactive interface.

You can set up the beer database as follows:

注意运行前先启动服务器：p1

运行完毕后关闭服务器：p0

$ createdb beer
$ psql beer -f /web/cs9315/24T1/pracs/p01/beer.dump
... around 20 lines include SET, CREATE TABLE, ALTER TABLE...
$ psql beer
SET
psql (15.6)
Type "help" for help.beer=# select count(*) from beers;count
-------24
(1 row)beer=# \d
... gives a list of tables in the database ...... explore/manipulate the database ...
beer=# \q
$