FAQ - (In)frequently Asked Questions

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 the postgres.conf and pg_hba.conf files located in data directory. In a BigSQL installation, the default data directory will be located within the installation directory.

Open the postgres.conf file and make sure that listen_addresses = '*' Open pg_hba.conf and add a line like:

$ host	all         	all           	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.


To create a new user:

postgres=# create user myuser with password 'mypassword';

To create a new database:

postgres=# create database mydb;

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));

You can create schema and add tables within the schema:

mydb=# create schema myschema;
mydb=# create table myschema.t1 (id int,name varchar(30));

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.


Now suppose you want to connect to another database. You can use \c command:

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 pg_log directory. According to standard BigSQL configurations about log_filename parameter (postgresql-%a.log) 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 --test

$ 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.

  1. Locate the data directory (by default it will be inside “data” directory within the BigSQL installation.)
  2. Rename the directory to a new name.
  3. Reinitialize the newly installed postgres version. This will create a new data directory.
  4. Delete the newly created data directory.
  5. Rename the old data directory to original name.
  6. 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