From puppetdb server:
sudo -u pe-postgres /opt/puppetlabs/server/apps/postgresql/bin/pg_dumpall --inserts -c -O -f  /var/tmp/third_attempt.sql -l pe-postgres

On postgres server, postgres running as user pe-postgres

psql -f /var/tmp/third_attempt.sql pe-postgres

NOTE: I am trying to figure out why I am getting the below errors:

psql:/var/tmp/third_attempt.sql:19041: ERROR:  relation "catalogs" does not exist
psql:/var/tmp/third_attempt.sql:19049: ERROR:  relation "catalogs" does not exist

psql:/var/tmp/third_attempt.sql:11995: ERROR:  relation "reports" does not exist
LINE 1: INSERT INTO reports VALUES (1572, '\xf3804e08338404cba213218...
                    ^
psql:/var/tmp/third_attempt.sql:11996: ERROR:  relation "reports" does not exist
LINE 1: INSERT INTO reports VALUES (1573, '\xbff4bbf1c4001a192da8ac1...
                    ^
psql:/var/tmp/third_attempt.sql:11997: ERROR:  relation "reports" does not exist
LINE 1: INSERT INTO reports VALUES (1574, '\xfca99c0f1ea8e330fee355e...
                    ^
psql:/var/tmp/third_attempt.sql:11998: ERROR:  relation "reports" does not exist
LINE 1: INSERT INTO reports VALUES (1575, '\xb4d81c370554fec817617d2...
                    ^
psql:/var/tmp/third_attempt.sql:11999: ERROR:  relation "reports" does not exist
LINE 1: INSERT INTO reports VALUES (1576, '\xb3a524b686069184c71202e...
                    ^
psql:/var/tmp/third_attempt.sql:12000: ERROR:  relation "reports" does not exist
LINE 1: INSERT INTO reports VALUES (1577, '\x74ac036ca2b1492a77cfa64...

psql:/var/tmp/third_attempt.sql:20013: ERROR:  unrecognized configuration parameter "lock_timeout"

The databases seem to get created as expected:

[pe-postgres@test16 ~]$ psql
psql (9.2.15)
Type "help" for help.

pe-postgres=# \l
                                             List of databases
      Name       |    Owner    | Encoding |   Collate   |    Ctype    |          Access privileges
-----------------+-------------+----------+-------------+-------------+-------------------------------------
 pe-activity     | pe-postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | "pe-postgres"=CTc/"pe-postgres"    +
                 |             |          |             |             | =T/"pe-postgres"                   +
                 |             |          |             |             | "pe-activity"=CTc/"pe-postgres"
 pe-classifier   | pe-postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | "pe-postgres"=CTc/"pe-postgres"    +
                 |             |          |             |             | =T/"pe-postgres"                   +
                 |             |          |             |             | "pe-classifier"=CTc/"pe-postgres"
 pe-orchestrator | pe-postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | "pe-postgres"=CTc/"pe-postgres"    +
                 |             |          |             |             | =T/"pe-postgres"                   +
                 |             |          |             |             | "pe-orchestrator"=CTc/"pe-postgres"
 pe-postgres     | postgres    | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 pe-puppetdb     | pe-postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | "pe-postgres"=CTc/"pe-postgres"    +
                 |             |          |             |             | =T/"pe-postgres"                   +
                 |             |          |             |             | "pe-puppetdb"=CTc/"pe-postgres"
 pe-rbac         | pe-postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | "pe-postgres"=CTc/"pe-postgres"    +
                 |             |          |             |             | =T/"pe-postgres"                   +
                 |             |          |             |             | "pe-rbac"=CTc/"pe-postgres"
 postgres        | postgres    | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 template0       | postgres    | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres                        +
                 |             |          |             |             | postgres=CTc/postgres
 template1       | postgres    | UTF8     | en_US.UTF-8 | en_US.UTF-8 | postgres=CTc/postgres              +
--More--