FAQ - (In)frequently Asked Questions
- What is BigSQL?
- How do I install BigSQL?
- What settings are tuned in BigSQL vs a plain vanilla PostgreSQL install?
- I installed PostgreSQL, but I can't connect to the database from my application server.
- After I install BigSQL, how do I create a new database/user/table?
- I'm trying to run some sql commands. How do I connect to the database (via psql)?
- Where are the PostgreSQL log files?
- Why am I getting a python error?
- How can I install more packages and extensions?
- How do I instruct BigSQL to use an existing data directory?
- How can I install experimental/beta quality components?
- How do I back up a PostgreSQL database?
- I uninstalled postgres binary which I was using for my database. Now that I reinstalled it, I am unable to start it.
- I tried to initialize PostgreSQL as root, and it failed. Now I'm unable initialize PostgreSQL at all.
What is BigSQL?
BigSQL is the most complete and developer-friendly Postgres distribution. This distribution puts Postgres at the core of the datacenter. The package includes Foreign Data Wrappers, which help integrate your datacenter with different types of database systems. This distribution makes it easy for users to install all popular Postgres related tools.
How do I install BigSQL?
BigSQL provides a sandbox, which is a single package (zip file) that includes all necessary files for installation. Download and unpack the files into a directory. Everything works out of the box.
In addition to the sandbox, BigSQL provides installers for Microsoft Windows and Mac OS X. These installers can help on OS, giving specific standard installation procedures such as creating shortcuts, services, and menu items.
What settings are tuned in BigSQL vs a plain vanilla PostgreSQL install?
The following parameters values are updated in BigSQL based on feedback from experts to improve the "out of the box" experience:
logging_collector = on log_filename = 'postgresql-%a.log' log_line_prefix = '%t [%p]: [%l-1] user=%u,db=%d,app=%a,client=%h ' log_checkpoints = on log_autovacuum_min_duration = 0 log_temp_files = 0 log_lock_waits = 0 checkpoint_segments = 16 maintenance_work_mem = 64MB max_wal_senders = 5 track_io_timing = on wal_keep_segments = 32 max_replication_slots = 5 wal_level = hot_standby log_lock_waits = on
I installed PostgreSQL, but I can't connect to the database from my application server.
Your application needs a hostname or IP address where Postgres service is running, as well as the port on which Postgres is listening. In addition your application needs to provide a valid username and password to connect to a server.
At the Postgres server side, Postgres should accept the connection from your
application server’s IP address and username combination. This is controlled by
pg_hba.conf files located in
data directory. In a BigSQL installation, the default data directory will be
located within the installation directory.
postgres.conf file and make sure that
listen_addresses = '*'
pg_hba.conf and add a line like:
$ host all all 0.0.0.0/0 md5
After these modifications, restart the PostgreSQL server. As part of the installation, you will be setting the password for the superuser: "postgres". This account can be used for testing the connection from the application server. However, creation of a different user account is suggested for security reasons.
After I install BigSQL, how do I create a new database/user/table?
BigSQL, like other Postgres installations, comes with a superuser "postgres" by default. You may want to create other user accounts for the application. You can do all these administrative activities using the superuser account.
First connect to postgres as superuser (postgres):
$ psql -U postgres psql (9.6) Type "help" for help. postgres=#
To create a new user:
postgres=# create user myuser with password 'mypassword'; CREATE ROLE
To create a new database:
postgres=# create database mydb; CREATE DATABASE
You can now connect to this database:
postgres=# \c mydb; You are now connected to database "mydb" as user "postgres".
Once in the database, you can create a new table:
mydb=# create table t1 (id int,name varchar(30)); CREATE TABLE
You can create schema and add tables within the schema:
mydb=# create schema myschema; CREATE SCHEMA mydb=# create table myschema.t1 (id int,name varchar(30)); CREATE TABLE mydb=#
I'm trying to run some sql commands. How do I connect to the database (via psql)?
You have to have the credentials to connect to database cluster (in postgres,
a "cluster" is a collection of databases, not to be confused with hardware
clusters). The default superuser is "postgres". When you connect to cluster as
the postgres user, you will be connected to a database with same name unless
specified using a
-d option. If you want to connect to a specific
database, you can use
-d option of psql:
$ psql -U postgres -d mydb psql (9.6) Type "help" for help. mydb=#
Now suppose you want to connect to another database. You can use
mydb=# \c postgres You are now connected to database "postgres" as user "postgres".
Where are the PostgreSQL log files?
Log files will be written to pg_log directory inside the data directory of the postgres cluster.
By default, the data directory will be located inside your installation directory. Look for
data/pg9x directory. Within this data directory there will be a
According to standard BigSQL configurations about log_filename parameter (
there will be one log file for every day for a week.
-rw-------. 1 vagrant vagrant 2998 May 19 21:25 postgresql-Thu.log -rw-------. 1 vagrant vagrant 2410570 May 17 23:25 postgresql-Tue.log -rw-------. 1 vagrant vagrant 356 May 18 22:11 postgresql-Wed.log
Why am I getting a python error?
The BigSQL Package comes with Python executables for Windows. If unpacking is not completed properly, you may receive the following error:
'python' is not recognized as an internal or external command, operable program or batch file.
Please try unpacking the sandbox again and allow it to complete before trying any command.
How can I install more packages and extensions?
You can install more packages and extensions using the PGC Command Line utility. In the PGCLI, you can issue following command to list all available components:
$ pgc list
Once you have the list of all installable components, you can install each of them using the following command.
$ pgc install perl5
How do I instruct BigSQL to use an existing data directory?
The PGC command line utility can be used for maintaining the location related meta data. You can modify/update the information by using the following command:
$ pgc config pg95 --datadir=data/pg95 --logdir=data/logs/pg95
This is required if you are reinstalling a postgres version.
How can I install test components?
Using the pgc command line utility, you can get the list of "Test" components available by passing a parameter
$ pgc list --test
How do I backup a PostgreSQL database?
There are multiple ways, including built-in pg_dump and pg_restore. For regular backups, we recommend the excellent pgBackRest tool of PostgreSQL databases. pgBackrest is available as a component from the pgc command line. Click here for detailed information on how to use pgBackrest.
I uninstalled postgres binary which I was using for my database. Now that I reinstalled it, I am unable to start it.
When you uninstall a postgres version, only executables will be removed. The data directory remains untouched. So if you reinstall, BigSQL will try to reuse the same data directory and it fails.
- Locate the data directory (by default it will be inside “data” directory within the BigSQL installation.)
- Rename the directory to a new name.
- Reinitialize the newly installed postgres version. This will create a new data directory.
- Delete the newly created data directory.
- Rename the old data directory to original name.
- Start postgres.
I tried to initialize PostgreSQL as root, and it failed. Now I'm unable initialize PostgreSQL at all.
Bigsql creates a new directory pg95 inside "data" directory (ex:
bigsql/data/pg95) to use it as the data directory for the postgres.
When you try to run
./pgc init pg95 as root user, it creates that directory as root and eventually the initialization fails. This directory which is owned by root will prevent any further initialization, and you may receive following error message:
OSError: [Errno 1] Operation not permitted: '/home/vagrant/bigsql/data/pg95'
Change the ownership back to the user:
$ chmod vagrant:vagrant pg95