Yes, pretty much everything that people actually use builds on DBI. However, there are a bunch of newer modules that can make your life easier with layers on top of DBI, such as DBIx::Simple
(convenience methods for queries, simpler and saner return values, statement caching and such) or DBIx::Class
(complete object-relational mapper using abstracted SQL and a whole ecosystem of extensions).
As for databases, it all depends on what you want to do. SQLite has become quite popular for smallish things that don't need much in terms of remote access and concurrency. Its databases live in one easily distributed file and basically create themselves.
Otherwise, yes, Postgres.