Setup PostgreSQL from pkgsrc
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=>
pg_hba.conf file, that you may want to change, is initialized by
initdb and can be found very protected under
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
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::
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
One line stuff
- i file.sql
- (psql command) import an external file
- s myvar foo
- (psql command)
set the var
foo. Use it with the with a collon before (
- $ pg_dump -s -t tablename dbname
- shows the DDL (Data Definition Language) for a single table.
ERROR: invalid byte sequence for encoding "UTF8"
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;