PostgreSQL-15.2

please add info or links:

It appears you are confusing two things:

• postgres the system user
• postgres the SQL database user

Typically installing postgres to your OS creates an «initial DB» and starts the postgres server daemon running. If not then you’ll need to run initdb

 # typically initdb creates a table named "postgres" owned by user "current logged in user name" # however, when you use pg_ctl it defaults to connecting as user "current user" to database "current user" so specify user postgres here # so that it avoids confusion of having them named differently. $ /usr/local/pgsql/bin/initdb -D /usr/local/pgsql/data -U postgres

and start the postgres daemon through some means, then proceed ref.

CAUTION The answer about changing the UNIX password for «postgres» through «$ sudo passwd postgres» is not preferred, and can even be DANGEROUS!

sudo -u postgres psql postgres
# \password postgres
Enter new password: 

To explain it a little bit. There are usually two default ways to login to PostgreSQL server:

So you never want to set the password for UNIX account «postgres». Leave it locked as it is by default.

Of course things can change if you configure it differently from the default setting. For example, one could sync the PostgreSQL password with UNIX password and only allow local logins. That would be beyond the scope of this question.

Introduction to PostgreSQL

is an advanced
object-relational database management system (ORDBMS), derived from
the Berkeley Postgres database management system.

This package is known to build and work properly using an LFS 11.3
platform.

Package Information

• Download (HTTP):
  https://ftp.postgresql.org/pub/source/v15.2/postgresql-15.2.tar.bz2

• Download MD5 sum: 968418dbdd700caaccfeabcee7516496

• Download size: 21.6 MB

• Estimated disk space required: 209 MB (add 42 MB for tests)

• Estimated build time: 0.6 SBU (with parallelism=4, add 0.1
  SBU for tests)

PostgreSQL Dependencies

Optional

ICU-72.1, libxml2-2.10.3, libxslt-1.1.37, OpenLDAP-2.6.4,
Linux-PAM-1.5.2, MIT
Kerberos V5-1.20.1 and Bonjour

Optional (To Regenerate Documentation)

fop-2.8,
docbook-4.5-dtd, docbook-dsssl-1.79, DocBook-utils-0.6.14, OpenJade-1.3.2, and SGMLSpm-1.1

Introduction to PostgreSQL

is an advanced
object-relational database management system (ORDBMS), derived from
the Berkeley Postgres database management system.

Note

Development versions of BLFS may not build or run some packages
properly if dependencies have been updated since the most recent
stable versions of the book.

Package Information

• Download (HTTP):
  https://ftp.postgresql.org/pub/source/v15.3/postgresql-15.3.tar.bz2

• Download MD5 sum: 7cb75773ff8fbe533701562c3278206b

• Download size: 22 MB

• Estimated disk space required: 213 MB (add 42 MB for tests)

• Estimated build time: 0.6 SBU (with parallelism=4, add 0.1
  SBU for tests)

PostgreSQL Dependencies

Optional

ICU-73.2, libxml2-2.10.4, libxslt-1.1.38, OpenLDAP-2.6.4,
Linux-PAM-1.5.3, MIT Kerberos
V5-1.21 and Bonjour

Optional (To Regenerate Documentation)

fop-2.8,
docbook-4.5-dtd, docbook-dsssl-1.79, DocBook-utils-0.6.14, OpenJade-1.3.2, and SGMLSpm-1.1

Editor Notes: https://wiki.linuxfromscratch.org/blfs/wiki/postgresql

Introduction to PostgreSQL

is an advanced
object-relational database management system (ORDBMS), derived from
the Berkeley Postgres database management system.

Note

Development versions of BLFS may not build or run some packages
properly if dependencies have been updated since the most recent
stable versions of the book.

Package Information

• Download (HTTP):
  https://ftp.postgresql.org/pub/source/v15.3/postgresql-15.3.tar.bz2

• Download MD5 sum: 7cb75773ff8fbe533701562c3278206b

• Download size: 22 MB

• Estimated disk space required: 213 MB (add 42 MB for tests)

• Estimated build time: 0.6 SBU (with parallelism=4, add 0.1
  SBU for tests)

PostgreSQL Dependencies

Optional

ICU-73.2, libxml2-2.10.4, libxslt-1.1.38, OpenLDAP-2.6.4,
Linux-PAM-1.5.3, MIT Kerberos
V5-1.21 and Bonjour

Optional (To Regenerate Documentation)

fop-2.8,
docbook-4.5-dtd, docbook-dsssl-1.79, DocBook-utils-0.6.14, OpenJade-1.3.2, and SGMLSpm-1.1

Editor Notes: https://wiki.linuxfromscratch.org/blfs/wiki/postgresql

The PostgreSQL Database — Installation and Configuration:

Ubuntu: (16.04, 14.04) Install: sudo apt-get install postgresql

• postgresql-9.X — libraries and SQL
• postgresql-common — the database program
• postgresql-client-9.X — utility programs and man pages
• postgresql-client-common — utility programs and man pages
• libpq5 — network client libraries

Starting the database: sudo service postgresql start

Red Hat Enterprise Linux 6 RPM packages:

• postgresql-8.4.11-1.el6_2.x86_64 — commands, HTML docs and man pages
• postgresql-server-8.4.11-1.el6_2.x86_64 — DB server and locale based messages
• postgresql-libs-8.4.11-1.el6_2.x86_64 — libraries and locale based messages
• postgresql-docs-8.4.11-1.el6_2.x86_64 — tutorials, examples and a monster PDF manual

Other RPM packages:

• postgresql-test — lots of examples.
• postgresql-jdbc — Java connectivity
• postgresql-plperl — Perl connectivity
• postgresql-plpython — Python connectivity
• postgresql-devel — C language connectivity

Starting the database (as root): service postgresql start

Once the database initialization has occurred, one can then start the database (as root): service postgresql restart

Stopping postgresql service: [ OK ]
Starting postgresql service: [ OK ] 

• User: postgres
• Home directory: /var/lib/pgsql
• Default shell: /bin/bash

A password will be missing. As root issue the command:

passwd postgres

postgres

PostgreSQL post install:

• Login as user postgres: su - postgres
  
  This will execute the profile: /var/lib/pgsql/.bash_profile

  PGLIB=/usr/lib/pgsql
  PGDATA=/var/lib/pgsql/data
  export PGLIB PGDATA 

• Initialize PostgreSQL database server: initdb --pgdata=/var/lib/pgsql/data
  
  This creates a bunch of directories, a template directory and sets up
  the postgres configuration in the user directory /var/lib/pgsql/.
  Red Hat start command (service)/script (rc script) will perform
  this task if it has not already been performed. See next step — Starting the database.

• Starting the database server: As root. (from most to least favorite method)
  
  service postgresql start
  
  (If the database has not already been initialized with initdb, this will be performed by the command)
  
  OR
  
  /etc/rc.d/init.d/postgresql start
  
  (If the database has not already been initialized with initdb, this will be performed by the script)
  
  OR
  
  /usr/bin/pg_ctl -D /var/lib/pgsql/data -p /usr/bin/postmaster -l logfile start &
  
  OR
  
  /usr/bin/postmaster -D /var/lib/pgsql/data &

  Notes:

  • Configuration file: /var/lib/pgsql/data/postgresql.conf
    
    By default there is no network access.
    See the directive tcpip_socket. (Required for ODBC,JDBC)
    Also see the postmaster directive «-i».
    Logging and tuning parameters are specified here.
  • Host Access file: /var/lib/pgsql/data/pg_hba.conf
  • Authentication/Identification file: /var/lib/pgsql/data/pg_ident.conf

Using the PostgreSQL Database:

postgres=# CREATE DATABASE bedrock;
CREATE DATABASEpostgres=# \c bedrock
You are now connected to database "bedrock" as user "postgres".bedrock=# CREATE USER dude WITH PASSWORD 'supersecret';
CREATE ROLEbedrock=# GRANT ALL PRIVILEGES ON employee to dude;
GRANTbedrock=# \z Access privileges Schema | Name | Type | Access privileges | Column privileges | Policies
--------+----------+-------+---------------------------+-------------------+---------- public | employee | table | postgres=arwdDxt/postgres+| | | | | dude=arwdDxt/postgres | |
(1 row)

• PostgreSQL additional info and documentation:
• Resources: /usr/share/pgsql/

Backup / Restore a PostgreSQL Database:

Backup / Dump a database:

• [prompt]$ pg_dump dbname > outfile
• [prompt]$ pg_dump dbname -h localhost -U postgres -t tablename > outfile
  
  if migrating the resulting SQL into another SQL database, add the option: --no-owner. This eliminates the ALTER OWNER or SET SESSION AUTHORIZATION statements.
• [prompt]$ pg_dumpall > outfile
• [prompt]$ pg_dumpall -Fc dbname > outfile

Restore a database:

• [prompt]$ psql dbname < infile
  
  where infile is the file output by the pg_dump command. The database dbname will not be created by this command but can be generated with: createdb dbname
• [prompt]$ psql -f infile postgres
  
  if you are loading into an empty cluster then «postgres» should usually be used.

pgadmin:

• Ubuntu: sudo apt install pgadmin3

• Allow X-Windows session to display session generated by another user (postgres): xhost +
• Switch user to postgres: sudo su - postgres
• pgadmin3
• File + Add Server:
  
  
• Traverse the «bedrock» database tree to show the table «employee»:
  
  
• With the «employee» table highlighted in the tree, select the grid icon «View the data in the selected object»:
  
  

PostgreSQL Man Pages:

Linux shell commands:

• createdb — Create a new PostgreSQL database
• createlang — Add a new programming language to a PostgreSQL database
• createuser — Create a new PostgreSQL user
• dropdb — Remove an existing PostgreSQL database
• droplang — Remove a programming language from a PostgreSQL database
• dropuser — Drops (removes) a PostgreSQL user
• pg_dump — Extract a PostgreSQL database into a script file. Example: pg_dump dbname > outfile
• pg_dumpall — Extract all PostgreSQL databases into a script file
• vacuumdb — Clean and analyze a PostgreSQL database
• psql — PostgreSQL interactive terminal

PostgreSQL SQL Statement Man Pages:

Before anyone can access the database, you must start the database server. The database server program is called postgres.

If you are using a pre-packaged version of , it almost certainly includes provisions for running the server as a background task according to the conventions of your operating system. Using the package’s infrastructure to start the server will be much less work than figuring out how to do this yourself. Consult the package-level documentation for details.

The bare-bones way to start the server manually is just to invoke postgres directly, specifying the location of the data directory with the -D option, for example:

$ postgres -D /usr/local/pgsql/data

Normally it is better to start postgres in the background. For this, use the usual Unix shell syntax:

$ postgres -D /usr/local/pgsql/data >logfile 2>&1 &

It is important to store the server’s and output somewhere, as shown above. It will help for auditing purposes and to diagnose problems. (See Section 25.3 for a more thorough discussion of log file handling.)

This shell syntax can get tedious quickly. Therefore the wrapper program is provided to simplify some tasks. For example:

pg_ctl start -l logfile

will start the server in the background and put the output into the named log file. The -D option has the same meaning here as for postgres. pg_ctl is also capable of stopping the server.

Normally, you will want to start the database server when the computer boots. Autostart scripts are operating-system-specific. There are a few example scripts distributed with in the contrib/start-scripts directory. Installing one will require root privileges.

su postgres -c 'pg_ctl start -D /usr/local/pgsql/data -l serverlog'

• For , look at the file contrib/start-scripts/freebsd in the source distribution.

• if [ -x /usr/local/pgsql/bin/pg_ctl -a -x /usr/local/pgsql/bin/postgres ]; then su -l postgres -c '/usr/local/pgsql/bin/pg_ctl start -s -l /var/postgresql/log -D /usr/local/pgsql/data' echo -n ' postgresql'
  fi

• On systems either add

  /usr/local/pgsql/bin/pg_ctl start -l logfile -D /usr/local/pgsql/data

  to /etc/rc.d/rc.local or /etc/rc.local or look at the file contrib/start-scripts/linux in the source distribution.

  [Unit]
  Description=PostgreSQL database server
  Documentation=man:postgres(1)
  After=network-online.target
  Wants=network-online.target
  [Service]
  Type=notify
  User=postgres
  ExecStart=/usr/local/pgsql/bin/postgres -D /usr/local/pgsql/data
  ExecReload=/bin/kill -HUP $MAINPID
  KillMode=mixed
  KillSignal=SIGINT
  TimeoutSec=infinity
  [Install]
  WantedBy=multi-user.target

  Using Type=notify requires that the server binary was built with configure --with-systemd.

  Consider carefully the timeout setting. has a default timeout of 90 seconds as of this writing and will kill a process that does not report readiness within that time. But a server that might have to perform crash recovery at startup could take much longer to become ready. The suggested value of infinity disables the timeout logic.

• On , use either the or start scripts, depending on preference.

• su - postgres -c "/usr/local/pgsql/bin/pg_ctl start -l logfile -D /usr/local/pgsql/data"

  Then, create a symbolic link to it in /etc/rc3.d as S99postgresql.

While the server is running, its PID is stored in the file postmaster.pid in the data directory. This is used to prevent multiple server instances from running in the same data directory and can also be used for shutting down the server.

19.3.1. Server Start-up Failures

There are several common reasons the server might fail to start. Check the server’s log file, or start it by hand (without redirecting standard output or standard error) and see what error messages appear. Below we explain some of the most common error messages in more detail.

LOG: could not bind IPv4 address "127.0.0.1": Address already in use
HINT: Is another postmaster already running on port 5432? If not, wait a few seconds and retry.
FATAL: could not create any TCP/IP sockets

This usually means just what it suggests: you tried to start another server on the same port where one is already running. However, if the kernel error message is not Address already in use or some variant of that, there might be a different problem. For example, trying to start a server on a reserved port number might draw something like:

$ postgres -p 666LOG: could not bind IPv4 address "127.0.0.1": Permission denied
HINT: Is another postmaster already running on port 666? If not, wait a few seconds and retry.
FATAL: could not create any TCP/IP sockets

A message like:

FATAL: could not create shared memory segment: Invalid argument
DETAIL: Failed system call was shmget(key=5440001, size=4011376640, 03600).

An error like:

FATAL: could not create semaphores: No space left on device
DETAIL: Failed system call was semget(5440126, 17, 03600).

does mean you’ve run out of disk space. It means your kernel’s limit on the number of semaphores is smaller than the number wants to create. As above, you might be able to work around the problem by starting the server with a reduced number of allowed connections (max_connections), but you’ll eventually want to increase the kernel limit.

Details about configuring IPC facilities are given in Section 19.4.1.

19.3.2. Client Connection Problems

Although the error conditions possible on the client side are quite varied and application-dependent, a few of them might be directly related to how the server was started. Conditions other than those shown below should be documented with the respective client application.

psql: error: connection to server at "server.joe.com" (123.123.123.123), port 5432 failed: Connection refused Is the server running on that host and accepting TCP/IP connections?

This is the generic “I couldn’t find a server to talk to” failure. It looks like the above when TCP/IP communication is attempted. A common mistake is to forget to configure the server to allow TCP/IP connections.

Alternatively, you might get this when attempting Unix-domain socket communication to a local server:

psql: error: connection to server on socket "/tmp/.s.PGSQL.5432" failed: No such file or directory Is the server running locally and accepting connections on that socket?

If the server is indeed running, check that the client’s idea of the socket path (here /tmp) agrees with the server’s unix_socket_directories setting.

This documentation is for an unsupported version of PostgreSQL.
You may want to view the same page for the
current
version, or one of the other supported versions listed above instead.

Installation

For a fresh install or upgrading from previous releases of
:

1. Running as
   root, bin,
   or any other account with special access rights is a security
   risk; don’t do it. The postmaster
   will in fact refuse to start as root.

2. Configure the source code for your system. It is this step
   at which you can specify your actual installation path for
   the build process and make choices about what gets installed.
   Change into the src subdirectory
   and type:

   > ./configure 

   followed by any options you might want to give it. For a
   first installation you should be able to do fine without any. For a
   complete list of options, type:

   > ./configure --help 

   Some of the more commonly used ones are:

   —prefix=BASEDIR

   Selects a different base directory for the
   installation of . The default is
   /usr/local/pgsql.

   —enable-locale

   If you want to use locales.

   —enable-multibyte

   Allows the use of multibyte character encodings. This
   is primarily for languages like Japanese, Korean, or
   Chinese.

   —with-perl

   Builds the Perl interface and plperl extension
   language. Please note that the Perl interface needs to be
   installed into the usual place for Perl modules
   (typically under /usr/lib/perl), so you must have root
   access to perform the installation step. (It is often
   easiest to leave out initially, and then build and
   install the Perl interface after completing the
   installation of PostgreSQL itself.)

   —with-odbc

   Builds the ODBC driver package.

   —with-tcl

   Builds interface libraries and programs requiring
   Tcl/Tk, including libpgtcl, pgtclsh, and pgtksh.

3. Compile the program. Type

   > gmake 

   The compilation process can take anywhere from 10 minutes to
   an hour. Your mileage will most certainly vary. Remember to use GNU
   make.

   The last line displayed will hopefully be

   All of PostgreSQL is successfully made. Ready to install. 

4. If you want to test the newly built server before you
   install it, you can run the regression tests at this point.
   The regression tests are a test suite to verify that
   runs on your
   machine in the way the developers expected it to. For
   detailed instructions see Regression Test. (Be sure to use the
   «parallel regress test» method, since the sequential method
   only works with an already-installed server.)

5. If you are not upgrading an existing system then skip to
   step 7.

   You now need to back up your existing database. To dump
   your fairly recent post-6.0 database installation, type

   > pg_dumpall > db.out 

   If you wish to preserve object id’s (oids), then use the -o
   option when running .
   However, unless you have a special reason for doing this (such as
   using OIDs as keys in tables), don’t do it.

6. If you are upgrading an existing system then kill the
   database server now. Type

   > ps ax | grep postmaster 

   or

   > ps -e | grep postmaster 

   (It depends on your system which one of these two works. No
   harm can be done by typing the wrong one.) This should list the
   process numbers for a number of processes, similar to this:

    263 ? SW 0:00 (postmaster) 777 p1 S 0:00 grep postmaster 

   Type the following line, with
   pid replaced by the process id for process postmaster (263 in the above case). (Do not
   use the id for the process «grep postmaster».)

   > kill pid 

   Tip: On systems which have started at boot time, there
   is probably a startup file that will accomplish the same
   thing. For example, on a Redhat Linux system one might find
   that

   > /etc/rc.d/init.d/postgres.init stop 

   works.

   > mv /usr/local/pgsql /usr/local/pgsql.old 

   (substitute your particular paths).

7. Install the
   executable files and libraries. Type

   > gmake install 

8. > LD_LIBRARY_PATH=/usr/local/pgsql/lib
   > export LD_LIBRARY_PATH 

   on sh, ksh, bash, zsh or

   > setenv LD_LIBRARY_PATH /usr/local/pgsql/lib 

   on csh or tcsh. You might want to put this into a shell
   startup file such as /etc/profile.

   /usr/local/pgsql/lib 

   Then run command /sbin/ldconfig.

   If in doubt, refer to the manual pages of your system. If
   you later on get a message like

   psql: error in loading shared libraries
   libpq.so.2.1: cannot open shared object file: No such file or directory 

   then the above was necessary. Simply do this step then.

9. > mkdir /usr/local/pgsql/data
   > chown postgres /usr/local/pgsql/data
   > su - postgres
   > /usr/local/pgsql/bin/initdb -D /usr/local/pgsql/data 

10. The previous step should have told you how to start up the
    database server. Do so now. The command should look something
    like

    > /usr/local/pgsql/bin/postmaster -D /usr/local/pgsql/data 

    This will start the server in the foreground. To make it
    detach to the background, you can use the option, but then you won’t see any log messages the
    server produces. A better way to put the server in the
    background is

    > nohup /usr/local/pgsql/bin/postmaster -D /usr/local/pgsql/data \ </dev/null >>server.log 2>>1 & 

11. If you are upgrading from an existing installation, dump
    your data back in:

    > /usr/local/pgsql/bin/psql -d template1 -f db.out 

    You also might want to copy over the old pg_hba.conf file and any other files you might have
    had set up for authentication, such as password files.

• > PATH=$PATH:/usr/local/pgsql/bin 

• You probably want to install the and
  documentation. Type

  > cd /usr/src/pgsql/postgresql-7.0/doc
  > gmake install 

  This will install files under /usr/local/pgsql/doc and /usr/local/pgsql/man. To enable your system to find
  the documentation, you need to
  add a line like the following to a shell startup file:

  > MANPATH=$MANPATH:/usr/local/pgsql/man 

  > cd /usr/local/pgsql/doc
  > gunzip -c user.ps.tz | lpr 

  Here is how you might do it if you have Ghostscript on your
  system and are writing to a laserjet printer.

  > gunzip -c user.ps.gz \ | gs -sDEVICE=laserjet -r300 -q -dNOPAUSE -sOutputFile=- \ | lpr 

  Printer setups can vary wildly from system to system. If in
  doubt, consult your manuals or your local expert.

• Usually, you will want to modify your computer so that it
  will automatically start the database server whenever it boots.
  This is not required; the server can be run successfully
  from non-privileged accounts without root intervention.

  It might be advisable to keep a log of the server output. To
  start the server that way try:

  > nohup su -c 'postmaster -D /usr/local/pgsql/data > server.log 2>&1' postgres & 

  Here are a few more operating system specific
  suggestions.

  • > su postgres -c "/usr/local/pgsql/bin/postmaster -S -D /usr/local/pgsql/data" 

  • #!/bin/sh
    [ -x /usr/local/pgsql/bin/postmaster ] && { su -l pgsql -c 'exec /usr/local/pgsql/bin/postmaster -D/usr/local/pgsql/data -S -o -F > /usr/local/pgsql/errlog' & echo -n ' pgsql'
    } 

    You may put the line breaks as shown above. The shell is
    smart enough to keep parsing beyond end-of-line if there is an
    expression unfinished. The exec saves one layer of shell under the
    postmaster process so the parent is init.

  • In RedHat Linux add a file /etc/rc.d/init.d/postgres.init which is
    based on the example in contrib/linux/. Then make a softlink to
    this file from /etc/rc.d/rc5.d/S98postgres.init.

• Run the regression tests against the installed server (using
  the sequential test method). If you didn’t run the tests before
  installation, you should definitely do it now. For detailed
  instructions see Regression
  Test.

To start experimenting with , set up the paths as explained above
and start the server. To create a database, type

> createdb testdb 

> psql testdb 

to connect to that database. At the prompt you can enter SQL
commands and start experimenting.

Contents

Short Descriptions

Command Explanations

--enable-thread-safety:
This switch makes the client libraries thread-safe by allowing
concurrent threads in libpq and ECPG
programs to safely control their private connection handles.

--with-openssl: builds the package with
support for encrypted
connections.

--with-perl: builds the PL/Perl
server-side language.

--with-python: builds the PL/Python
server-side language. Python3 is used by default, Python2 is no
longer supported.

--with-tcl: builds the PL/Tcl
server-side language.

Contents

Short Descriptions

Command Explanations

--enable-thread-safety:
This switch makes the client libraries thread-safe by allowing
concurrent threads in libpq and ECPG
programs to safely control their private connection handles.

--with-openssl: builds the package with
support for encrypted
connections.

--with-perl: builds the PL/Perl
server-side language.

--with-python: builds the PL/Python
server-side language. Python3 is used by default, Python2 is no
longer supported.

--with-tcl: builds the PL/Tcl
server-side language.

Contents

Short Descriptions

First steps after installation

First connect/login as root

 # su - postgres $ psql psql (9.6.0) Type "help" for help.

 # or in windows, current user doesn't matter c:\path\to\psql.exe -U postgres psql (9.6.0) Type "help" for help.

 postgres=# help You are using psql, the command-line interface to PostgreSQL. Type: \copyright for distribution terms \h for help with SQL commands \? for help with psql commands \g or terminate with semicolon to execute query \q to quit

Create a schema called test in the default database called postgres

 postgres=# CREATE SCHEMA test;

 postgres=# CREATE USER xxx PASSWORD 'yyy';

Grant privileges (like the ability to create tables) on new schema to new role

 postgres=# GRANT ALL ON SCHEMA test TO xxx;

Grant privileges (like the ability to insert) to tables in the new schema to the new role

 postgres=# GRANT ALL ON ALL TABLES IN SCHEMA test TO xxx;

 postgres=# \q

 # su - xxx

 xxx$ psql -d postgres psql (9.2.4) Type "help" for help.

 # in windows # c:\path\to\psql.exe -U xxx -d postgres # again, windows doesn't care what your current user is, by default psql (9.6.0) Type "help" for help.

Create a table test in schema test

 postgres=>CREATE TABLE test.test (coltest varchar(20)); CREATE TABLE

Insert a single record into new table

 postgres=>insert into test.test (coltest) values ('It works!'); INSERT 0 1

First SELECT from a table

 postgres=>SELECT * from test.test; coltest ----------- It works! (1 row)

Drop test table

 postgresql=>DROP TABLE test.test; DROP TABLE

Configuring PostgreSQL

Config
Files

$PGDATA/pg_ident.con, $PGDATA/pg_hba.conf, and $PGDATA/postgresql.conf

Install the /etc/rc.d/init.d/postgresql init script
included in the blfs-bootscripts-20230101 package:

make install-postgresql

Starting
the PostgreSQL Server and Creating a Sample Database

su - postgres -c '/usr/bin/postgres -D /srv/pgsql/data > \ /srv/pgsql/data/logfile 2>&1 &'

Note

If you are scripting this part, you should wait for the server
to start before going on, by adding for example after the above
command.

su - postgres -c '/usr/bin/createdb test' &&
echo "create table t1 ( name varchar(20), state_province varchar(20) );" \ | (su - postgres -c '/usr/bin/psql test ') &&
echo "insert into t1 values ('Billy', 'NewYork');" \ | (su - postgres -c '/usr/bin/psql test ') &&
echo "insert into t1 values ('Evanidus', 'Quebec');" \ | (su - postgres -c '/usr/bin/psql test ') &&
echo "insert into t1 values ('Jesse', 'Ontario');" \ | (su - postgres -c '/usr/bin/psql test ') &&
echo "select * from t1;" | (su - postgres -c '/usr/bin/psql test')

When you are done with testing, you can shut down the server, by
issuing as root:

su - postgres -c "/usr/bin/pg_ctl stop -D /srv/pgsql/data"

Command Explanations

--enable-thread-safety:
This switch makes the client libraries thread-safe by allowing
concurrent threads in libpq and ECPG
programs to safely control their private connection handles.

--with-openssl: builds the package with
support for encrypted
connections.

--with-perl: builds the PL/Perl
server-side language.

--with-python: builds the PL/Python
server-side language. Python3 is used by default, Python2 is no
longer supported.

--with-tcl: builds the PL/Tcl
server-side language.

Configuring PostgreSQL

Config
Files

$PGDATA/pg_ident.con, $PGDATA/pg_hba.conf, and $PGDATA/postgresql.conf

Starting
the PostgreSQL Server and Creating a Sample Database

su - postgres -c '/usr/bin/postgres -D /srv/pgsql/data > \ /srv/pgsql/data/logfile 2>&1 &'

Note

If you are scripting this part, you should wait for the server
to start before going on, by adding for example after the above
command.

su - postgres -c '/usr/bin/createdb test' &&
echo "create table t1 ( name varchar(20), state_province varchar(20) );" \ | (su - postgres -c '/usr/bin/psql test ') &&
echo "insert into t1 values ('Billy', 'NewYork');" \ | (su - postgres -c '/usr/bin/psql test ') &&
echo "insert into t1 values ('Evanidus', 'Quebec');" \ | (su - postgres -c '/usr/bin/psql test ') &&
echo "insert into t1 values ('Jesse', 'Ontario');" \ | (su - postgres -c '/usr/bin/psql test ') &&
echo "select * from t1;" | (su - postgres -c '/usr/bin/psql test')

When you are done with testing, you can shut down the server, by
issuing as root:

su - postgres -c "/usr/bin/pg_ctl stop -D /srv/pgsql/data"

Configuring PostgreSQL

Config
Files

$PGDATA/pg_ident.con, $PGDATA/pg_hba.conf, and $PGDATA/postgresql.conf

Install the /etc/rc.d/init.d/postgresql init script
included in the blfs-bootscripts-20230101 package:

make install-postgresql

Starting
the PostgreSQL Server and Creating a Sample Database

su - postgres -c '/usr/bin/postgres -D /srv/pgsql/data > \ /srv/pgsql/data/logfile 2>&1 &'

Note

If you are scripting this part, you should wait for the server
to start before going on, by adding for example after the above
command.

su - postgres -c '/usr/bin/createdb test' &&
echo "create table t1 ( name varchar(20), state_province varchar(20) );" \ | (su - postgres -c '/usr/bin/psql test ') &&
echo "insert into t1 values ('Billy', 'NewYork');" \ | (su - postgres -c '/usr/bin/psql test ') &&
echo "insert into t1 values ('Evanidus', 'Quebec');" \ | (su - postgres -c '/usr/bin/psql test ') &&
echo "insert into t1 values ('Jesse', 'Ontario');" \ | (su - postgres -c '/usr/bin/psql test ') &&
echo "select * from t1;" | (su - postgres -c '/usr/bin/psql test')

When you are done with testing, you can shut down the server, by
issuing as root:

su - postgres -c "/usr/bin/pg_ctl stop -D /srv/pgsql/data"

Installation of PostgreSQL

groupadd -g 41 postgres &&
useradd -c "PostgreSQL Server" -g postgres -d /srv/pgsql/data \ -u 41 postgres

Note

There are several configuration items that add additional
functionality with optional packages to . Use to see a
list.

sed -i '/DEFAULT_PGSOCKET_DIR/s@/tmp@/run/postgresql@' src/include/pg_config_manual.h &&
./configure --prefix=/usr \ --enable-thread-safety \ --docdir=/usr/share/doc/postgresql-15.3 &&
make

There are a number of programs in the contrib/ directory. If you are going to run this
installation as a server and wish to build some of them, enter
make -C contrib or
make -C contrib/<SUBDIR-NAME>
for each subdirectory.

Note

• The actual data files are stored in /srv/pgsql/data

• The upgraded data files will be stored in /srv/pgsql/newdata

• There is enough disk space to hold the actual data files
  twice. The upgrade is not an inline upgrade but it will
  copy the data to new database files.

First, do a temporary install which makes access to the new
binaries much easier:

make DESTDIR=$(pwd)/DESTDIR install

install -d -o postgres $(pwd)/DESTDIR/tmp

pushd $(pwd)/DESTDIR/tmp
systemctl stop postgresql
su postgres -c "../usr/bin/initdb -D /srv/pgsql/newdata"
su postgres -c "../usr/bin/pg_upgrade \ -d /srv/pgsql/data -b /usr/bin \ -D /srv/pgsql/newdata -B ../usr/bin"
popd

At this point, your database files are available in two locations
on disk. The old data is located in /srv/pgsql/data, and the new data is in
/srv/pgsql/newdata. Backing up the
old database files is suggested before continuing.

rm -rf /srv/pgsql/data
mv /srv/pgsql/newdata /srv/pgsql/data

make install &&
make install-docs

make -C contrib/<SUBDIR-NAME> install

Tip

If you only intend to use as a client to connect to a
server on another machine, your installation is complete and you
should not run the remaining commands.

If you have upgraded an existing database, skip the rest of the
commands because your database is ready to use. If this is the
first time you install ,
continue with the initialization.

install -v -dm700 /srv/pgsql/data &&
install -v -dm755 /run/postgresql &&
chown -Rv postgres:postgres /srv/pgsql /run/postgresql

su - postgres -c '/usr/bin/initdb -D /srv/pgsql/data'

Installation of PostgreSQL

groupadd -g 41 postgres &&
useradd -c "PostgreSQL Server" -g postgres -d /srv/pgsql/data \ -u 41 postgres

Note

There are several configuration items that add additional
functionality with optional packages to . Use to see a
list.

sed -i '/DEFAULT_PGSOCKET_DIR/s@/tmp@/run/postgresql@' src/include/pg_config_manual.h &&
./configure --prefix=/usr \ --enable-thread-safety \ --docdir=/usr/share/doc/postgresql-15.2 &&
make

There are a number of programs in the contrib/ directory. If you are going to run this
installation as a server and wish to build some of them, enter
make -C contrib or
make -C contrib/<SUBDIR-NAME>
for each subdirectory.

Note

• The actual data files are stored in /srv/pgsql/data

• The upgraded data files will be stored in /srv/pgsql/newdata

• There is enough disk space to hold the actual data files
  twice. The upgrade is not an inline upgrade but it will
  copy the data to new database files.

First, do a temporary install which makes access to the new
binaries much easier:

make DESTDIR=$(pwd)/DESTDIR install

install -d -o postgres $(pwd)/DESTDIR/tmp

pushd $(pwd)/DESTDIR/tmp
/etc/rc.d/init.d/postgresql stop
su postgres -c "../usr/bin/initdb -D /srv/pgsql/newdata"
su postgres -c "../usr/bin/pg_upgrade \ -d /srv/pgsql/data -b /usr/bin \ -D /srv/pgsql/newdata -B ../usr/bin"
popd

At this point, your database files are available in two locations
on disk. The old data is located in /srv/pgsql/data, and the new data is in
/srv/pgsql/newdata. Backing up the
old database files is suggested before continuing.

rm -rf /srv/pgsql/data
mv /srv/pgsql/newdata /srv/pgsql/data

make install &&
make install-docs

make -C contrib/<SUBDIR-NAME> install

Tip

If you only intend to use as a client to connect to a
server on another machine, your installation is complete and you
should not run the remaining commands.

If you have upgraded an existing database, skip the rest of the
commands because your database is ready to use. If this is the
first time you install ,
continue with the initialization.

install -v -dm700 /srv/pgsql/data &&
install -v -dm755 /run/postgresql &&
chown -Rv postgres:postgres /srv/pgsql /run/postgresql

su - postgres -c '/usr/bin/initdb -D /srv/pgsql/data'

Installation of PostgreSQL

groupadd -g 41 postgres &&
useradd -c "PostgreSQL Server" -g postgres -d /srv/pgsql/data \ -u 41 postgres

Note

There are several configuration items that add additional
functionality with optional packages to . Use to see a
list.

sed -i '/DEFAULT_PGSOCKET_DIR/s@/tmp@/run/postgresql@' src/include/pg_config_manual.h &&
./configure --prefix=/usr \ --enable-thread-safety \ --docdir=/usr/share/doc/postgresql-15.3 &&
make

There are a number of programs in the contrib/ directory. If you are going to run this
installation as a server and wish to build some of them, enter
make -C contrib or
make -C contrib/<SUBDIR-NAME>
for each subdirectory.

Note

• The actual data files are stored in /srv/pgsql/data

• The upgraded data files will be stored in /srv/pgsql/newdata

• There is enough disk space to hold the actual data files
  twice. The upgrade is not an inline upgrade but it will
  copy the data to new database files.

First, do a temporary install which makes access to the new
binaries much easier:

make DESTDIR=$(pwd)/DESTDIR install

install -d -o postgres $(pwd)/DESTDIR/tmp

pushd $(pwd)/DESTDIR/tmp
/etc/rc.d/init.d/postgresql stop
su postgres -c "../usr/bin/initdb -D /srv/pgsql/newdata"
su postgres -c "../usr/bin/pg_upgrade \ -d /srv/pgsql/data -b /usr/bin \ -D /srv/pgsql/newdata -B ../usr/bin"
popd

At this point, your database files are available in two locations
on disk. The old data is located in /srv/pgsql/data, and the new data is in
/srv/pgsql/newdata. Backing up the
old database files is suggested before continuing.

rm -rf /srv/pgsql/data
mv /srv/pgsql/newdata /srv/pgsql/data

make install &&
make install-docs

make -C contrib/<SUBDIR-NAME> install

Tip

If you only intend to use as a client to connect to a
server on another machine, your installation is complete and you
should not run the remaining commands.

If you have upgraded an existing database, skip the rest of the
commands because your database is ready to use. If this is the
first time you install ,
continue with the initialization.

install -v -dm700 /srv/pgsql/data &&
install -v -dm755 /run/postgresql &&
chown -Rv postgres:postgres /srv/pgsql /run/postgresql

su - postgres -c '/usr/bin/initdb -D /srv/pgsql/data'