It is usually good practice to create a SUPERUSER role to manage the database instead of logging in as postgres. The cluster will also automatically have a postgres user. The cluster will be initialized to use trust authentication, which means that all local users can log in without a password. If you want to be able to access the database without specifying the port number, make sure to drop the default cluster first. Note that the dummy clusters I have “created” above use ports 54, because 5432 was already taken. Remember that port 5432 is the default PostgreSQL port. down postgres /data/tmp /var/log/postgresql/ĩ.5 tmp2 5434 down postgres /data/tmp2 /var/log/postgresql/ I have also mocked up what you would see if you create two additional clusters: Ver Cluster Port Status Owner Data directory Log fileĩ.5 pg_data 5432 online postgres /data/pg_data /var/log/postgresql/ĩ. If you have run the commands above, you should see the first line in the following output. If you did not drop the default cluster, or have other clusters on this server, the new cluster will be assinged 5433 or the next available port number.Īfter executing these commands, you can see the results using pg_lsclusters (no options, does not need sudo).
Because we have dropped the default cluster, the new cluster will be assigned port 5432. New clusters are assigned the next available port beginning with 5432. The second statement will create the folder /pg_data in the /data partition, set the folder owner to the postgres system user, initialize the cluster, and start it. The first statement will stop the default cluster and drop it (destroying all data in the process). Sudo pg_createcluster -d /data -start 9.5 pg_data
A folder of this name should not exist,* and will be created for you. In the example below, I create a cluster named pg_data. For example, if you want to have your cluster on a partition named /data, you should create your cluster in a folder on that partition, not as the root folder /data. PG documentation recommends not creating a cluster at the root of a partition.
If you are attempting an upgrade (i.e., not a fresh install) but do not know whether your data is backed up, STOP and make sure that you have a backup!įirst, decide where you are going to store your data.
This assumes that you have done a fresh install and the main cluster created during install is empty, or that you have a backup of your database as I demonstrated at the beginning of this article.
WARNING: I am about to show you how to delete a database cluster. If you follow the PostgreSQL docs for initializing a new cluster, it will work, but your new cluster will not start at system startup, and if your database client connects to localhost:5432 the connection will go to the default cluster created during installation. But it’s fairly common to run a server with a small root partition, and have the database cluster, which can grow very large, on a separate data partition. When you install Postgres on a Debian-based Linux distro, the installer will automatically create a database cluster named main with a data directory in /var/lib/postgresql/9.5/main. If you use pgAdmin III for server management, that has to be added separately, as I have done here.īefore you can restore your database you have to have a cluster to restore it to. Since the PostGIS package depends on PostgreSQL, you should get everything you need. Then install PostgreSQL and PostGIS: sudo apt-get install postgresql-9.5-postgis-2.2 postgresql-contrib-9.5 pgadmin3
sudo add-apt-repository ppa:ubuntugis/ubuntugis-unstable In spite of the name, the repository is very stable and widely used.
This will give you access to the latest PostGIS, GDAL/OGR drivers, SpatiaLite, QGIS, etc. If you are not yet using the ubuntugis-unstable PPA, go ahead and set that up. If you’re not installing/upgrading an OS, your next step might involve going straight to installing/upgrading PostgreSQL 9.5/PostGIS 2.2. The extension *.backup is arbitrary, but is the one that is expected by pgAdmin.Īfter that the next step for me was installing Linux Mint 17. In the terminal: pg_dump -Fc my_db > my_db.backup If you are upgrading from a previous version of PostgreSQL/PostGIS and have data you want to bring over to your new cluster, you have to begin with a dump of your old data. As with the previous time I upgraded the OS, I wanted to upgrade PostgreSQL and PostGIS as well. I recently upgraded Linux Mint to version 18. NOTE: This guide is not tested for Ubuntu, but should work the same way.