Postgress Admin Notes

Client only install:

apt-get install -y --no-install-recommends postgresql-client

 

Basic commands

# Show databases
\l

# Switch databases
\c dbname

# Show tables
\dt

# Describe table
\d tablename

# Switch to expanded display (similar to MySQL \g)
\x

 

Get/Set settings

SELECT name, setting FROM pg_settings WHERE name = 'random_page_cost';
SET random_page_cost = 1.0;

 

Database exports and dumps

# Dump from MySQl
mysqldump --compatible=postgresql --default-character-set=utf8 -r databasename.mysql -u root databasename

# Dump specific table
sudo -u postgres pg_dump -t radcheck radius  > radcheck.sql

# Dump as INSERT statements, without the table schema
sudo -u postgres pg_dump --column-inserts -a -t radcheck radius | less

Convert the MySQL dumps to PostgreSQL compatable INSERTs

# This gives data as follows...
#INSERT INTO radcheck VALUES (15,'user1@realm.net','Password','==','pass1');
#INSERT INTO radcheck VALUES (14,'user2@realm.net','Password','==','pass2');
# It needs to be as follows...
#INSERT INTO radcheck (id, username, attribute, op, value) VALUES (72, 'user1@realm.net', 'Cleartext-Password', ':=', 'pass1');
#INSERT INTO radcheck (id, username, attribute, op, value) VALUES (74, 'user2@realm.net', 'Cleartext-Password', ':=', 'pass2');

grep "INSERT" ../mysql_export/radcheck.sql | sed -r "s/INSERT INTO radcheck/INSERT INTO radcheck \(username, attribute, op, value\)/g" | sed -r "s/Password/Cleartext-Password/g" | sed -r "s/==/:=/g" | awk -F',' 'BEGIN{OFS=",";} {print $1, $2, $3 ", value) VALUES (" $5, $6, $7, $8;}' | less

# Produces...
#INSERT INTO radcheck (username, attribute, op, value) VALUES ('user1@realm.net','Cleartext-Password',':=','pass1');
#INSERT INTO radcheck (username, attribute, op, value) VALUES ('user2@realm.net','Cleartext-Password',':=','pass2');

# Generate table 'userinfo' data from usernames in radcheck table
grep "INSERT" ../mysql_export/radcheck.sql | awk -F"'" '{print "INSERT INTO userinfo (username, name, mail, department, workphone, homephone, mobile) VALUES ('"'"'" $2 "'"'"', '"'"''"'"', '"'"''"'"', '"'"''"'"', '"'"''"'"', '"'"''"'"', '"'"''"'"');"}' | less
# Gives output
INSERT INTO userinfo (username, name, mail, department, workphone, homephone, mobile) VALUES ('user1@realm.net', '', '', '', '', '', '');

# Generate entries for radusergroup for user to group affinity
grep "INSERT" ../mysql_export/usergroup.sql | sed -r "s/INSERT INTO usergroup VALUES \(/INSERT INTO radusergroup \(username, groupname, priority\) VALUES \('/g" | awk -F"'" '{print $1 "'"'"'" $3 "'"'"'" ", " "'"'"'" $5 "'"'"'" ", 0);";}' | less

# Give the output
INSERT INTO radusergroup (username, groupname, priority) VALUES ('user1@realm1.net', 'group1', 0);
INSERT INTO radusergroup (username, groupname, priority) VALUES ('user1@realm2.net', 'group2', 0);

# Generate the radreply per-user attributes for RADIUS
grep "INSERT" ../mysql_export/radreply.sql | sed -r "s/INSERT INTO radreply VALUES \(/INSERT INTO radreply \(username, attribute, op, value\) VALUES \('/g" | awk -F"'" '{print $1 "'"'"'" $3 "'"'"'" ", " "'"'"'" $5 "'"'"'" ", " "'"'"'" $7 "'"'"'" ", " "'"'"'" $9 "'"'"'" ");";}' > radreply.sql

# Produces output like
INSERT INTO radreply (username, attribute, op, value) VALUES ('user1@realm.net', 'Framed-IP-Address', '=', '10.253.5.7');
INSERT INTO radreply (username, attribute, op, value) VALUES ('user2@realm.net', 'Framed-IP-Address', '=', '10.253.5.6');

Import from file

psql -f import.sql
sudo -u postgres psql -d my-database -f table1.sql

Show row count for each table

radius=# select count(*) from radpostauth;
  count
----------
 25570185
(1 row)

SELECT schemaname,relname,n_live_tup FROM pg_stat_user_tables ORDER BY n_live_tup DESC;

radius=# SELECT schemaname,relname,n_live_tup
radius-#   FROM pg_stat_user_tables
radius-#   ORDER BY n_live_tup DESC;
 schemaname |    relname    | n_live_tup
------------+---------------+------------
 public     | radpostauth   |   25676717
 public     | radacct       |   17481035
 public     | radreply      |      37794
 public     | radcheck      |       4531
 public     | userinfo      |       4530
 public     | radusergroup  |       4522
 public     | radgroupreply |        254
 public     | radgroupcheck |          2
 public     | mtotacct      |          0
 public     | badusers      |          0
 public     | totacct       |          0
(11 rows)

Delete/empty tables...

DROP TABLE IF EXISTS radacct RESTRICT;

DELETE FROM radacct;

TRUNCATE radacct RESTRICT;

---