Monday, September 26, 2016

Moving a PostgreSQL table from an Ubuntu machine to a RHEL machine

pg_dump -Fc --file=(/.../pg_dump_file) --table=(tablename) --dbname=(database name)
(sftp the pg_dump_file to the new machine)
(install postgres by doing all of the below side bonus)
pg_restore --clean --create --dbname=(database name) --jobs=3 (pg_dump_file)

the -Fc means "binary format, not text"

Side bonus: installing PostgreSQL 9.4 on RHEL 6 (Red Hat Enterprise Linux,) (tips from here)
sudo yum localinstall https://download.postgresql.org/pub/repos/yum/9.4/redhat/rhel-6-x86_64/pgdg-redhat94-9.4-3.noarch.rpm # figure out what rpm you need here; if you just yum install postgresql you'll get an old version
sudo yum install postgresql94 postgresql94-server
sudo service postgresql-9.4 initdb # one-time initialization
sudo service postgresql-9.4 start # make the postgres server go
sudo service postgresql-9.4 on # make the postgres server go every time I log on

Note: on RHEL 7, you have to do these 3 lines instead of those last 3:
sudo /usr/pgsql-9.4/bin/postgresql94-setup initdb
sudo systemctl start postgresql-9.4.service
systemctl enable postgresql-9.4.service # so it starts at boot

sudo yum install postgresql94-contrib # for hstore
sudo rpm -ivh http://dl.fedoraproject.org/pub/epel/epel-release-latest-6.noarch.rpm # to get the right EPEL rpm for RHEL 6; it's like adding a new APT repository in Ubuntu I think
sudo yum install postgis2_94 # more help here if you need

sudo su - postgres
psql
CREATE USER (myusername) WITH SUPERUSER;
CREATE DATABASE (dbname);
(go to that new database)
CREATE EXTENSION hstore;
CREATE EXTENSION postgis;
(quit postgres, e.g. with ctrl-d)
(log out of being the postgres user; e.g. with ctrl-d again)

Double note: I have 2 cloud machines, one on RHEL 6 and one on RHEL 7. The RHEL 7 one killed me at the "install postgis" step; adding the EPEL rpm didn't make all the build errors go away. (when I poked around for other EPEL rpms, I eventually found one that fixed most of the build errors, but then gave me another error where it was missing libpoppler.so-46. I think I was the first person ever to have this error. In a turn of lunacy, I spent part of the day building GDAL from source. Surprisingly, it kinda worked. But didn't get me to PostGIS.) So, if you googled this and are looking for an answer, I'm afraid I can't help. Sorry!