For example, I have a Linux server with a PostgreSQL server installed. How can I integrate it with AWS AppSync? Or can I only use Amazon RDS for this?
You can have a lambda datasource which can talk to your postgreSQL server on your self hosted EC2 instance. If you go to AWS AppSync console, it has a sample which uses RDS, but you can easily point that to PostgreSQL database, & so long as the lambda function and the linux server are deployed to the same VPC it should work.
Here is the sample on Github.