puppetdb(postgres)

[root@puppetdb ~]# sudo -u postgres psql

postgres=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
———–+———-+———-+———+——-+———————–
postgres | postgres | UTF8 | en_IN | en_IN |
puppetdb | postgres | UTF8 | en_IN | en_IN | =T/postgres +
| | | | | postgres=CTc/postgres+
| | | | | puppetdb=CTc/postgres
template0 | postgres | UTF8 | en_IN | en_IN | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | en_IN | en_IN | =c/postgres +
| | | | | postgres=CTc/postgres
(4 rows)

postgres=# \c puppetdb
You are now connected to database “puppetdb” as user “postgres”.
puppetdb=# \dt
List of relations
Schema | Name | Type | Owner
——–+———————–+——-+———-
public | catalog_resources | table | puppetdb
public | catalogs | table | puppetdb
public | certnames | table | puppetdb
public | edges | table | puppetdb
public | environments | table | puppetdb
public | fact_paths | table | puppetdb
public | fact_values | table | puppetdb
public | facts | table | puppetdb
public | factsets | table | puppetdb
public | latest_reports | table | puppetdb
public | report_statuses | table | puppetdb
public | reports | table | puppetdb
public | resource_events | table | puppetdb
public | resource_params | table | puppetdb
public | resource_params_cache | table | puppetdb
public | schema_migrations | table | puppetdb
public | value_types | table | puppetdb
(17 rows)

puppetdb=# \d
List of relations
Schema | Name | Type | Owner
——–+—————————-+———-+———-
public | catalog_resources | table | puppetdb
public | catalogs | table | puppetdb
public | catalogs_transform_id_seq1 | sequence | puppetdb
public | certnames | table | puppetdb
public | edges | table | puppetdb
public | environments | table | puppetdb
public | environments_id_seq | sequence | puppetdb
public | fact_paths | table | puppetdb
public | fact_paths_id_seq | sequence | puppetdb
public | fact_values | table | puppetdb
public | fact_values_id_seq | sequence | puppetdb
public | facts | table | puppetdb
public | factsets | table | puppetdb
public | factsets_id_seq | sequence | puppetdb
public | latest_reports | table | puppetdb
public | report_statuses | table | puppetdb
public | report_statuses_id_seq | sequence | puppetdb
public | reports | table | puppetdb
public | resource_events | table | puppetdb
public | resource_params | table | puppetdb
public | resource_params_cache | table | puppetdb
public | schema_migrations | table | puppetdb
public | value_types | table | puppetdb
(23 rows)

puppetdb=# SELECT * FROM pg_catalog.pg_tables WHERE schemaname != ‘pg_catalog’ AND schemaname != ‘information_schema’;
schemaname | tablename | tableowner | tablespace | hasindexes | hasrules | hastriggers
————+———————–+————+————+————+———-+————-
public | schema_migrations | puppetdb | | t | f | f
public | resource_events | puppetdb | | t | f | t
public | latest_reports | puppetdb | | t | f | t
public | resource_params | puppetdb | | t | f | t
public | resource_params_cache | puppetdb | | t | f | t
public | edges | puppetdb | | t | f | t
public | catalog_resources | puppetdb | | t | f | t
public | report_statuses | puppetdb | | t | f | t
public | reports | puppetdb | | t | f | t
public | catalogs | puppetdb | | t | f | t
public | value_types | puppetdb | | t | f | t
public | certnames | puppetdb | | t | f | t
public | environments | puppetdb | | t | f | t
public | factsets | puppetdb | | t | f | t
public | fact_paths | puppetdb | | t | f | t
public | fact_values | puppetdb | | t | f | t
public | facts | puppetdb | | t | f | t
(17 rows)

puppetdb=# select * from certnames;
name | deactivated
————————————+—————————-
server.puppethub.in |
puppetdb.puppethub.in |
omd2.puppethub.in | 2016-11-30 20:22:00.619+00
omd3.puppethub.in | 2016-11-30 20:22:34.328+00
omd2 | 2016-11-30 20:35:12.221+00
omd.puppethub.in |
vnc | 2016-12-02 23:02:11.956+00
vncserver.puppethub.in | 2016-12-02 23:24:51.246+00
vnc.ip.secureserver.net | 2016-12-05 12:19:11.104+00
vnc.puppethub.in |
li1287-237.members.linode.com | 2016-12-22 06:32:13.057+00
git.puppethub.in |
elk.c.rich-operand-154505.internal |
(13 rows)

puppetdb=# select name from certnames where deactivated is not null;
name
——————————-
omd2.puppethub.in
omd3.puppethub.in
omd2
vnc
vncserver.puppethub.in
vnc.ip.secureserver.net
li1287-237.members.linode.com
(7 rows)

puppetdb=# select * from catalogs;
id | hash | api_version | catalog_version | transaction_uuid | timestamp
| certname | environment_id | producer_timestamp
—-+——————————————+————-+—————–+————————————–+————————-
—+————————————+—————-+————————
3 | 77b3e49f6a97cae206f55c0e4fd55bcee3819533 | 1 | 1486147024 | 66d84c7d-77da-4689-b5aa-d4cc5b2d3592 | 2017-02-06 17:49:44.075+
00 | puppetdb.puppethub.in | 1 | 2017-02-06 17:49:43+00
1 | b91a689b3d5aeae129265c0232b9393f8e2a6da2 | 1 | 1486147024 | fe51c5b9-848d-405a-b903-21a12ef59bfe | 2017-02-06 17:50:25.563+
00 | server.puppethub.in | 1 | 2017-02-06 17:50:25+00
11 | 72a2aa1bee4214e177f9a488d47f3ab92a5b289b | 1 | 1486147024 | af372f5a-549e-42ca-a960-f4c56e5637d5 | 2017-02-06 17:51:06.992+
00 | git.puppethub.in | 1 | 2017-02-06 17:51:06+00
2 | 7a5326445f550168199b0857f1076b6e165dde99 | 1 | 1486147024 | 4a00f651-bea2-4618-b3ac-05c3da2b8c88 | 2017-02-06 18:07:05.248+
00 | omd.puppethub.in | 1 | 2017-02-06 18:07:05+00
12 | f7aa4c28106da978d3ed7a39413082561c2c42a0 | 1 | 1485981961 | a88f574b-f871-46f1-b6fc-a1eb05b89c7a | 2017-02-02 23:15:09.409+
00 | elk.c.rich-operand-154505.internal | 1 | 2017-02-02 23:15:09+00
10 | cc16ebb58bf24627719ae19475f44f455c7da7d4 | 1 | 1481120142 | 09a40554-1396-45fc-a038-092777981a02 | 2016-12-07 14:15:43.629+
00 | vnc.puppethub.in | 1 | 2016-12-07 14:15:43+00
(6 rows)

   I am going to deactivate one node from puppetserver (vnc.puppethub.in)

[root@server ~]# puppet cert list –all
+ “elk.c.rich-operand-154505.internal” (SHA256) C3:2B:EC:72:E3:33:D0:23:46:F1:DD:92:1C:EB:3C:F6:B5:69:FE:63:E4:BA:04:B9:83:0D:71:4D:9D:49:66:6B
+ “git.puppethub.in” (SHA256) 92:06:33:40:02:3A:39:DA:D4:81:11:CD:7F:95:64:30:A7:96:E8:09:E9:80:6C:2B:F2:0F:FE:D8:A9:11:6E:20
+ “omd.puppethub.in” (SHA256) 14:AA:4D:F8:6A:49:42:E9:EB:55:8E:9B:E9:38:B8:F0:64:21:3E:5E:79:45:E3:DF:8A:1E:E2:A6:D7:40:1B:92
+ “puppetdb.puppethub.in” (SHA256) 26:F3:E6:3D:71:80:BB:FF:25:BB:59:03:8D:D7:2C:7D:90:32:B4:36:F2:1C:EC:BF:81:7D:46:43:22:25:FE:A4
+ “server.puppethub.in” (SHA256) 48:26:28:17:FD:72:C2:63:60:D1:E9:41:DF:CB:46:3B:22:E0:B1:F9:C2:72:A6:AE:78:E6:34:FE:00:B5:BF:E7 (alt names: “DNS:puppet”, “DNS:server.puppethub.in”)
+ “vnc.puppethub.in” (SHA256) D1:CB:3D:4A:DB:5C:4E:76:E7:B0:B0:3D:0F:C5:2D:D2:23:56:95:71:EF:83:99:3F:54:F0:0E:AD:47:3A:8B:DD

[root@server ~]# puppet node clean vnc.puppethub.in
Notice: Revoked certificate with serial 14
Notice: Removing file Puppet::SSL::Certificate vnc.puppethub.in at ‘/var/lib/puppet/ssl/ca/signed/vnc.puppethub.in.pem’
vnc.puppethub.in

[root@server ~]# puppet node deactivate vnc.puppethub.in
Submitted ‘deactivate node’ for vnc.puppethub.in with UUID 72e169ff-c299-40b1-bb9d-5c8a02c630c3

[root@server ~]# puppet cert list –all
+ “elk.c.rich-operand-154505.internal” (SHA256) C3:2B:EC:72:E3:33:D0:23:46:F1:DD:92:1C:EB:3C:F6:B5:69:FE:63:E4:BA:04:B9:83:0D:71:4D:9D:49:66:6B
+ “git.puppethub.in” (SHA256) 92:06:33:40:02:3A:39:DA:D4:81:11:CD:7F:95:64:30:A7:96:E8:09:E9:80:6C:2B:F2:0F:FE:D8:A9:11:6E:20
+ “omd.puppethub.in” (SHA256) 14:AA:4D:F8:6A:49:42:E9:EB:55:8E:9B:E9:38:B8:F0:64:21:3E:5E:79:45:E3:DF:8A:1E:E2:A6:D7:40:1B:92
+ “puppetdb.puppethub.in” (SHA256) 26:F3:E6:3D:71:80:BB:FF:25:BB:59:03:8D:D7:2C:7D:90:32:B4:36:F2:1C:EC:BF:81:7D:46:43:22:25:FE:A4
+ “server.puppethub.in” (SHA256) 48:26:28:17:FD:72:C2:63:60:D1:E9:41:DF:CB:46:3B:22:E0:B1:F9:C2:72:A6:AE:78:E6:34:FE:00:B5:BF:E7 (alt names: “DNS:puppet”, “DNS:server.puppethub.in”)

In puppetdb

puppetdb=# select * from certnames;
name | deactivated
————————————+—————————-
server.puppethub.in |
puppetdb.puppethub.in |
omd2.puppethub.in | 2016-11-30 20:22:00.619+00
omd3.puppethub.in | 2016-11-30 20:22:34.328+00
omd2 | 2016-11-30 20:35:12.221+00
omd.puppethub.in |
vnc | 2016-12-02 23:02:11.956+00
vncserver.puppethub.in | 2016-12-02 23:24:51.246+00
vnc.ip.secureserver.net | 2016-12-05 12:19:11.104+00
li1287-237.members.linode.com | 2016-12-22 06:32:13.057+00
git.puppethub.in |
elk.c.rich-operand-154505.internal |
vnc.puppethub.in | 2017-02-06 18:17:58.958+00
(13 rows)
puppetdb=# select name from certnames where deactivated is not null;
name
——————————-
omd2.puppethub.in
omd3.puppethub.in
omd2
vnc
vncserver.puppethub.in
vnc.ip.secureserver.net
li1287-237.members.linode.com
vnc.puppethub.in
(8 rows)

puppetdb=# select * from catalogs;
id | hash | api_version | catalog_version | transaction_uuid | timestamp
| certname | environment_id | producer_timestamp
—-+——————————————+————-+—————–+————————————–+————————-
—+————————————+—————-+————————
12 | f7aa4c28106da978d3ed7a39413082561c2c42a0 | 1 | 1485981961 | a88f574b-f871-46f1-b6fc-a1eb05b89c7a | 2017-02-02 23:15:09.409+
00 | elk.c.rich-operand-154505.internal | 1 | 2017-02-02 23:15:09+00
2 | 1687a0ab57ca82b980b49819f044274548b113d1 | 1 | 1486147024 | 676a40d8-9cd1-4960-a8cb-d729c7f9d03f | 2017-02-06 18:19:19.273+
00 | omd.puppethub.in | 1 | 2017-02-06 18:19:19+00
3 | dcb1290edc917a8675798936d298a282999d4fd4 | 1 | 1486147024 | 1416ca5a-6445-48d0-9b5f-28442f38597e | 2017-02-06 18:19:40.575+
00 | puppetdb.puppethub.in | 1 | 2017-02-06 18:19:40+00
1 | b91a689b3d5aeae129265c0232b9393f8e2a6da2 | 1 | 1486147024 | f7db890b-0bb1-4a0f-99b7-144d17d98c58 | 2017-02-06 18:20:25.799+
00 | server.puppethub.in | 1 | 2017-02-06 18:20:25+00
11 | 72a2aa1bee4214e177f9a488d47f3ab92a5b289b | 1 | 1486147024 | 8c175e80-cf73-4d09-b399-d79f066f9f34 | 2017-02-06 18:21:06.756+
00 | git.puppethub.in | 1 | 2017-02-06 18:21:06+00
10 | cc16ebb58bf24627719ae19475f44f455c7da7d4 | 1 | 1481120142 | 09a40554-1396-45fc-a038-092777981a02 | 2016-12-07 14:15:43.629+
00 | vnc.puppethub.in | 1 | 2016-12-07 14:15:43+00
(6 rows)
puppetdb=# delete from catalogs where certname in (select name from certnames where deactivated is not null);
DELETE 1

puppetdb=# select * from catalogs;
id | hash | api_version | catalog_version | transaction_uuid | timestamp
| certname | environment_id | producer_timestamp
—-+——————————————+————-+—————–+————————————–+————————-
—+————————————+—————-+————————
12 | f7aa4c28106da978d3ed7a39413082561c2c42a0 | 1 | 1485981961 | a88f574b-f871-46f1-b6fc-a1eb05b89c7a | 2017-02-02 23:15:09.409+
00 | elk.c.rich-operand-154505.internal | 1 | 2017-02-02 23:15:09+00
2 | 1687a0ab57ca82b980b49819f044274548b113d1 | 1 | 1486147024 | 676a40d8-9cd1-4960-a8cb-d729c7f9d03f | 2017-02-06 18:19:19.273+
00 | omd.puppethub.in | 1 | 2017-02-06 18:19:19+00
3 | dcb1290edc917a8675798936d298a282999d4fd4 | 1 | 1486147024 | 1416ca5a-6445-48d0-9b5f-28442f38597e | 2017-02-06 18:19:40.575+
00 | puppetdb.puppethub.in | 1 | 2017-02-06 18:19:40+00
1 | b91a689b3d5aeae129265c0232b9393f8e2a6da2 | 1 | 1486147024 | f7db890b-0bb1-4a0f-99b7-144d17d98c58 | 2017-02-06 18:20:25.799+
00 | server.puppethub.in | 1 | 2017-02-06 18:20:25+00
11 | 72a2aa1bee4214e177f9a488d47f3ab92a5b289b | 1 | 1486147024 | 8c175e80-cf73-4d09-b399-d79f066f9f34 | 2017-02-06 18:21:06.756+
00 | git.puppethub.in | 1 | 2017-02-06 18:21:06+00
(5 rows)

[root@puppetdb ~]# curl ‘http://localhost:8080/v3/nodes’
[ {
“name” : “elk.c.rich-operand-154505.internal”,
“deactivated” : null,
“catalog_timestamp” : “2017-02-02T23:15:09.409Z”,
“facts_timestamp” : “2017-02-02T23:15:08.530Z”,
“report_timestamp” : null
}, {
“name” : “git.puppethub.in”,
“deactivated” : null,
“catalog_timestamp” : “2017-02-06T18:51:06.728Z”,
“facts_timestamp” : “2017-02-06T18:51:05.593Z”,
“report_timestamp” : null
}, {
“name” : “omd.puppethub.in”,
“deactivated” : null,
“catalog_timestamp” : “2017-02-06T19:07:05.508Z”,
“facts_timestamp” : “2017-02-06T19:07:01.045Z”,
“report_timestamp” : null
}, {
“name” : “puppetdb.puppethub.in”,
“deactivated” : null,
“catalog_timestamp” : “2017-02-06T18:49:43.522Z”,
“facts_timestamp” : “2017-02-06T18:49:37.857Z”,
“report_timestamp” : null
}, {
“name” : “server.puppethub.in”,
“deactivated” : null,
“catalog_timestamp” : “2017-02-06T18:50:25.674Z”,
“facts_timestamp” : “2017-02-06T18:50:24.480Z”,
“report_timestamp” : null

postgres=# help
You are using psql, the command-line interface to PostgreSQL.
Type: \copyright for distribution terms
\h for help with SQL commands
\? for help with psql commands
\g or terminate with semicolon to execute query
\q to quit

[root@puppetdb ~]# su – postgres
-bash-4.2$ ls -l
total 4
drwx—— 4 postgres postgres 4096 Nov 29 19:51 9.4

-bash-4.2$ psql puppetdb -c “select * from factsets;” > mydata

-bash-4.2$ ls
9.4 mydata
-bash-4.2$ cat mydata
id | certname | timestamp | environment_id | producer_timestamp
—-+————————————+—————————-+—————-+————————
10 | vnc.puppethub.in | 2016-12-07 14:15:42.424+00 | 1 | 2016-12-07 14:15:42+00
6 | vnc | 2016-12-02 22:59:30.087+00 | 1 | 2016-12-02 22:59:29+00
5 | omd3.puppethub.in | 2016-11-30 13:35:12.762+00 | 1 | 2016-11-30 13:35:12+00
7 | vncserver.puppethub.in | 2016-12-02 23:06:38.967+00 | 1 | 2016-12-02 23:06:38+00
2 | omd.puppethub.in | 2017-02-06 18:37:00.902+00 | 1 | 2017-02-06 18:37:00+00
3 | puppetdb.puppethub.in | 2017-02-06 18:49:37.857+00 | 1 | 2017-02-06 18:49:37+00
1 | server.puppethub.in | 2017-02-06 18:50:24.48+00 | 1 | 2017-02-06 18:50:24+00
11 | git.puppethub.in | 2017-02-06 18:51:05.593+00 | 1 | 2017-02-06 18:51:05+00
8 | li1287-237.members.linode.com | 2016-12-05 15:31:59.954+00 | 1 | 2016-12-05 15:31:59+00
4 | omd2.puppethub.in | 2016-11-30 10:18:35.646+00 | 1 | 2016-11-30 10:18:34+00
9 | vnc.ip.secureserver.net | 2016-12-05 12:17:45.101+00 | 1 | 2016-12-05 12:17:44+00
12 | elk.c.rich-operand-154505.internal | 2017-02-02 23:15:08.53+00 | 1 | 2017-02-02 23:15:08+00
(12 rows)

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s