Chapter 20 PostgreSQL

psql -h localhost -U postgres pasword ```

https://askubuntu.com/questions/831262/how-to-install-pgadmin-4-in-desktop-mode-on-ubuntu

$cd pgadmin4/
$source bin/activate
$python3 lib/python3.6/site-packages/pgadmin4/pgAdmin4.py
Starting pgAdmin 4. Please navigate to http://127.0.0.1:5050 in your browser.
  • Where is the database data stored in filesystem?

http://veryr.com/posts/postgres-storage-location/

00:13 # l /var/lib/postgresql/10/main/base/16385/
./         12897     2606_vm   2657      2756      3381      3602_vm
../        12899     2607      2658      2757      3381_vm   3603
112        12900     2607_fsm  2659      2830      3394      3603_fsm
113        12902     2607_vm   2660      2830_vm   3394_fsm  3603_vm
1247       12904     2608      2661      2831      3394_vm   3604
1247_fsm   1417      2608_fsm  2662      2832      3395      3605
1247_vm    1417_vm   2608_vm   2663      2832_vm   3439      3606
1249       1418      2609      2664      2833      3439_vm   3607
1249_fsm   1418_vm   2609_fsm  2665      2834      3440      3608
1249_vm    16407     2609_vm   2666      2834_vm   3455      3609
1255       16410     2610      2667      2835      3456      3712
1255_fsm   16412     2610_fsm  2668      2836      3456_fsm  3764
1255_vm    16415     2610_vm   2669      2836_vm   3456_vm   3764_fsm
1259       16417     2611      2670      2837      3466      3764_vm
1259_fsm   16418     2611_vm   2673      2838      3466_vm   3766
1259_vm    174       2612      2674      2838_fsm  3467      3767
12870      175       2612_fsm  2675      2838_vm   3468      3997
12870_fsm  2187      2612_vm   2678      2839      3501      5002
12870_vm   2224      2613      2679      2840      3501_vm   548
12872      2224_vm   2613_vm   2680      2840_fsm  3502      549
12874      2328      2615      2681      2840_vm   3503      6102
12875      2328_vm   2615_fsm  2682      2841      3534      6102_vm
12875_fsm  2336      2615_vm   2683      2995      3541      6104
12875_vm   2336_vm   2616      2684      2995_vm   3541_fsm  6104_vm
12877      2337      2616_fsm  2685      2996      3541_vm   6106
12879      2600      2616_vm   2686      3079      3542      6106_vm
12880      2600_fsm  2617      2687      3079_fsm  3574      6110
12880_fsm  2600_vm   2617_fsm  2688      3079_vm   3575      6111
12880_vm   2601      2617_vm   2689      3080      3576      6112
12882      2601_fsm  2618      2690      3081      3576_vm   6113
12884      2601_vm   2618_fsm  2691      3085      3596      6117
12885      2602      2618_vm   2692      3118      3596_vm   826
12885_fsm  2602_fsm  2619      2693      3118_vm   3597      826_vm
12885_vm   2602_vm   2619_fsm  2696      3119      3598      827
12887      2603      2619_vm   2699      3164      3598_vm   828
12889      2603_fsm  2620      2701      3256      3599      pg_filenode.map
12890      2603_vm   2620_vm   2702      3256_vm   3600      pg_internal.init
12890_fsm  2604      2650      2703      3257      3600_fsm  PG_VERSION
12890_vm   2604_vm   2651      2704      3258      3600_vm
12892      2605      2652      2753      3350      3601
12894      2605_fsm  2653      2753_fsm  3350_vm   3601_fsm
12895      2605_vm   2654      2753_vm   3351      3601_vm
12895_fsm  2606      2655      2754      3379      3602
12895_vm   2606_fsm  2656      2755      3380      3602_fsm

00:10 # du -sh /var/lib/postgresql/10/main/base/16385/
7.6M    /var/lib/postgresql/10/main/base/16385/

00:10 # psql -h localhost -U postgres
Password for user postgres: 
psql (10.1)
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)
Type "help" for help.

postgres=# select oid, datname from pg_database ;
  oid  |  datname  
-------+-----------
 13035 | postgres
 16385 | mydb
     1 | template1
 13034 | template0
(4 rows)
  • Create a new database

createdb -h localhost -U postgres -T template0 springfield

Postgres 中的简单压缩算法

http://veryr.com/posts/simple_lz_compress/