Postgres: Difference between revisions

From Bitpost wiki
No edit summary
Line 16: Line 16:
  mkdir default_configure && cd default_configure
  mkdir default_configure && cd default_configure
  ../configure --disable-documentation && make && sudo make install
  ../configure --disable-documentation && make && sudo make install
* set up a data folder where postgres will put all its data:
* set up a data folder where postgres will put all its data and its log:
  # Set up a symlink to a larger RAID drive
  # You must have a postgres user; on gentoo:
  mkdir /spiceflow/softraid/development/postgres-data
sudo useradd postgres
  sudo mkdir /usr/local/pgsql
cd /usr/local/pgsql
# Here, we are choosing to use a larger RAID drive
  ln -s /spiceflow/softraid/development/postgres-data data
  mkdir /spiceflow/softraid/development/postgres
  mkdir /spiceflow/softraid/development/postgres/data
  chown -R postgres:postgres /spiceflow/softraid/development/postgres


=== START SERVER ===
=== START SERVER ===
You must have a postgres user:
# on gentoo
sudo useradd postgres
If you installed as above, this is the command to start the server:
If you installed as above, this is the command to start the server:
  su postgres -c '/usr/local/bin/pg_ctl start -l /usr/local/pgsql/log.txt -D /usr/local/pgsql/data'
  su postgres -c '/usr/local/bin/pg_ctl start -l /spiceflow/softraid/development/postgres/log/log.txt -D /spiceflow/softraid/development/postgres/data'


=== NOSQL via JSON ===
=== NOSQL via JSON ===

Revision as of 20:18, 6 September 2018

DEV INSTALL

cd ~/development/Libraries/c
git clone git@github.com:postgres/postgres.git
cd postgres
mkdir default_configure && cd default_configure
../configure --prefix=/usr/local && make && sudo make install
# may need: sudo apt install libreadline-dev bison flex
# use apt search libreadline (etc)
cd ~/development/Libraries/c++
git clone git@github.com:jtv/libpqxx.git
cd libpqxx
# add pg bin folder to path
export PATH=$PATH:/usr/local/bin
mkdir default_configure && cd default_configure
../configure --disable-documentation && make && sudo make install
  • set up a data folder where postgres will put all its data and its log:
# You must have a postgres user; on gentoo:
sudo useradd postgres

# Here, we are choosing to use a larger RAID drive
mkdir /spiceflow/softraid/development/postgres
mkdir /spiceflow/softraid/development/postgres/data
chown -R postgres:postgres /spiceflow/softraid/development/postgres

START SERVER

If you installed as above, this is the command to start the server:

su postgres -c '/usr/local/bin/pg_ctl start  -l /spiceflow/softraid/development/postgres/log/log.txt -D /spiceflow/softraid/development/postgres/data'

NOSQL via JSON

GORGEOUS POSTGRES JSON QUERY! inject blobs of JSON, then you can crack open the json inside a cell!

   CREATE TABLE stocks (
     id integer NOT NULL,
     board_id integer NOT NULL,
     data jsonb
   );
   INSERT INTO stocks VALUES (1, 1, '{"symbol": "GOOG", "tags": ["hot", "trending"], "owned": true}');
   # returns count of data that HAS the field ingredients
   SELECT count(*) FROM stocks WHERE data ? 'ingredients';
   # ->> will return the property as integer or text (the parsed form of the attribute).
   SELECT * FROM stocks WHERE data->>'owned' = 'true';         # if data.owned == true
   # -> will return the attribute as a JSON object
   SELECT jsonb_array_elements_text(data->'tags') as tag FROM stocks WHERE id = 1;
   # returns the actual tag values, eg:
       tag
       --------------
       Improvements
       Office
       (2 rows)
   # NEXT: EXPLORE INDEXING!
   https://blog.codeship.com/unleash-the-power-of-storing-json-in-postgres/