Created at:

PostgreSQL notes

Setup PostgreSQL

Setup PostgreSQL from pkgsrc

(2015-11-06)

After installing PostgreSQL from pkgsrc, do, as root:: 

    # cp /usr/pkg/share/examples/rc.d/pgsql /etc/rc.d/
    # echo pgsql=YES >> /etc/rc.conf
    # /etc/rc.d/pgsql start

In NetBSD, at the first time pgsql script is run, it creates all necessary databases and files. Now the server is running.

The default role (username?) in the database is "pgsql". To list all available databases, do (not as root anymore):: 

    $ psql -U pgsql --list

We can connect to the postgres database to create other databases:: 

    $ psql -U pgsql postgres
    postgres=# CREATE ROLE user LOGIN; -- be careful -> no password here
    postgres=# CREATE DATABASE school WITH OWNER user;
    postgres=# GRANT ALL ON DATABASE school TO user;

Now, if you login with user "user" to the database "school", login may succed:: 

    $ psql -U user school
    school=>

The pg_hba.conf file, that you may want to change, is initialized by initdb and can be found very protected under /usr/pkg/pgsql/data. Also, do not forget to edit postgresql.conf (same directory) and set the listen variable if you want to allow remote connections, that are allowed to localhost only by default.

PostgreSQL: Documentation: 9.2: The pga_hba.conf File

The postgresql.conf file is also useful. It holds a lot of the server configuration options, like how to log statements, errors, etc.

Setup of PostgreSQL server in Linux from pre-built binary

After downloading the binary, when installing it, if you get an error you can see if the generator created a /tmp/bittrock_* file, with detailed logs.

In my case, I got an error similar to that:

Problem running post-install step. Installation may not complete correctly. The database cluster initialisation failed.

PostgreSQL 8.3.4-1 OS X installer fails post install step

Setup by hand (without pkg manager post-install configuration)

If you chose to install from either pre-built binary or by compiling the sources, you will need to create the databases files yourself. To do this, export the PGDATA variable to the directory where you want to setup the files. This variable is used when you setup or use the database, so you may want to put it in your .profile. Then, call the initdb Postgres' program:: 

    $ export PGDATA=/foo/bar
    $ initdb -U postgres

The -U option is important because, if it is not there, initdb will use the current user name as the superuser name of the database, breaking the convention that Postgres' superuser is called "postgres". Then, it creates the files (including the postgresql.conf file). The last lines of output will say that it is fine to start the server now. If the PGDATA env variable is set, it is not necessary to pass the -D parameter. So:: 

    $ postgres

Now, we just enter at the postgres database, as the postgres user:: 

    $ psql -U postgres

We are in. Now we create a role (kind like a user) and its database for our application:: 

    $ createuser -P -S -D -R -U postgres <username>
    $ createdb -U postgres -O <username> <databasename>

Then, login works:: 

    $ psql -U <username>

A page__ in PostgreSQL documentation helped.

PostgreSQL: Documentation: 8.3: Architectural Fundamentals

Basics

One line stuff

(2016-01-06)

i file.sql
(psql command) import an external file
s myvar foo
(psql command) set the var myvar to value foo. Use it with the with a collon before (:myvar).
$ pg_dump -s -t tablename dbname
shows the DDL (Data Definition Language) for a single table.

Troubleshooting

Error ERROR: invalid byte sequence for encoding "UTF8"

(2017-07-09)

When trying to import an external file using the psql prompt, I got the following error on some lines:: 

    ... ERROR:  invalid byte sequence for encoding "UTF8" ...

The problem was that the SQL file I was trying to import had ISO-8859-1 (Latin-1) encoding and my PostgreSQL setup (both server and client). So, to fix that, I had to change my client's setting in psql:: 

    set client_encoding = 'latin-1';

To query both server's and client's settings, you can type:: 

    show server_encoding;
    show client_encoding;