PostgreSQL-15.2

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]

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]

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:

YoLinux.com Tux database image

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 DATABASE
postgres=# \c bedrock
You are now connected to database "bedrock" as user "postgres".
bedrock=# CREATE USER dude WITH PASSWORD 'supersecret';
CREATE ROLE
bedrock=# GRANT ALL PRIVILEGES ON employee to dude;
GRANT
bedrock=# \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:

    PostgreSQL-15.2
  • Traverse the «bedrock» database tree to show the table «employee»:

    PostgreSQL-15.2
  • With the «employee» table highlighted in the tree, select the grid icon «View the data in the selected object»:

    PostgreSQL-15.2

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 666
LOG:  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]

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]

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]

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]

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]

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]

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]

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]

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]

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]

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]

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]

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'

Оцените статью
Master Hi-technology
Добавить комментарий