Install Postgresql (Ubuntu 20.04)
- sudo apt install postgresql postgresql-contrib
- sudo systemctl enable postgresql.service
Initial PostgreSQL Setup (Ubuntu 20.04)
- I change the access control porton of the /etc/postgresql/12/main/pg_hba.conf to the following:
- # Database administrative login by Unix domain socketlocal all postgres peer
- # TYPE DATABASE USER ADDRESS METHOD
- # "local" is for Unix domain socket connections only
- #local all all peer
- # IPv4 local connections:
- #host all all 127.0.0.1/32 md5
- # IPv6 local connections:
- #host all all ::1/128 md5
- # Allow replication connections from localhost, by a user with the
- # replication privilege.
- #local replication postgres peer
- #host replication postgres 127.0.0.1/32 md5
- #host replication postgres ::1/128 md5
- local all postgres peer
- local all all md5
- host all all 127.0.0.1/32 md5
- host all all ::1/128 md5
- and then restart the postgres service
- systemctl restart postgresql.service
Entering psql as admin
- sudo su - postgres -c "psql"
Create User and Database
- Using Shell
- This creates a user called "appdba" and a database called "appdb".
- The suggested password to use is "appdba11".
- Execute the following (as user root):sudo su - postgres -c "createuser -s -d -P appdba"
- sudo su - postgres -c "createdb -O appdba appdb"
- Using PSQL:
- Run psql as postgres user:sudo su - postgres -c psql
- Create user:create user awsdba with password 'mypassword';
- Change user password:alter user awsdba with password 'newpassword';
- Create database:create database awsdb with owner=awsdba;
- I find setting the pager with the following settings helps a lot:
- in ~/.bashrc, append# .shaexport PATH=$PATH:~/.bin:/usr/local/binexport PAGER=lessexport LESS="-iMSx4 -FX"
- in ~/.psqlrc:\timing# \pset pager always
- I commented the last line as I'd rather the pager only be used when the output overflows the screen
Originally posted here: https://www.facebook.com/note.php?note_id=380421129801
Assuming you have database on hostA and want to copy the entire schema over, but only the data for certain tables:
- Create the database on hostBsudo su - postgres "createuser --pwprompt --superuser --createdb newdba -h hostB"createdb -U newdba newdb kj dddddeteletethis
- Migrate the entire schema (without data) across:pg_dump -C -s -U olddba -h hostA olddb | psql -U newdba newdb
- Migrate data in certain tables (i.e. NOT climate* and forecast* tables) only:pg_dump -c -Tforecast\* -Tclimate\* -U olddba -h hostA olddb | psql -U newdba newdb
- You'll need to have the databases accessible from the other host for this to work, tweak your pg_hba.conf.
- you'll also need to set listen_addresses='*' inside postgresql.conf
- you may need to type "reset" after the pg_dump is complete to reset your terminal to echo mode; having two password prompts confuses it a bit.
SQL Tips and Tricks
Timestamps and Timezones
- I have a field of type timestamp with time zone and need to insert a value into it:
- Format your data in to "YYYY-MM-DD HH24:MI:SS TZ".
- If you insert it as just a single string value, postgres knows what to do
- INSERT INTO table (data_timestamp) VALUES ('2015-11-15 14:23:00 UTC');
- If you're assembling the string, you need a cast:
- INSERT INTO table (data_timestamp) VALUES ( ('2015-11-15 ' || '14:23:00' || ' UTC') :: timestamp with time zone);
- Conditional Inserts can be achieved as follows:
- INSERT INTO table (field1, field2) SELECT value1, value2 WHERE condition
- INSERT INTO queue_send_to_archive (data_timestamp,station_id,enqueued_timestamp,filename,msg) SELECT,,current_timestamp,,WHERE EXISTS (SELECT * FROM datalogger_properties WHERE property_name='send1MinDataToArchive' AND property_value='Y')